This is the short version of an overly long question that sadly attracted no answers.
Is it possible, given two jsonb variables x and y, to have both
1. (x = y) yield true, and
2. (x::text = y::text) yield false
I ask this question because it appears there is no promised order in which a jsonb object will be unpacked into a string. I'd just like to be sure this is the case.
Thanks in advance for feedback!
Edit:
The original question covers the "why" for this question, but the skinny is that I hope to group data in different rows based upon a hash of many columns represented as text, some of which are jsonb.
I don't care which way the object comes in or which way it gets unpacked, but I do care if two jsonb fields which are equivalent as jsonb are not equivalent as text strings.
As it seems I cannot count on text representations to be presented in the same way, I've normalized out the jsonb field to a separate table with the jsonb field set as a unique index.
And if I write more here... this question will approach the length of the one it derives from!
Formally the order is not deterministic because of the JSON object definition:
An object is an unordered set of name/value pairs.
Practically it appears that objects are sorted by length of keys and then alphabetically:
with example(col) as (
values
('{"cc": 1, "ab": 1, "a": 1, "aa": 1, "b": 2, "abc": 1}'::jsonb)
)
select col::text
from example
col
-------------------------------------------------------
{"a": 1, "b": 2, "aa": 1, "ab": 1, "cc": 1, "abc": 1}
(1 row)
Note that this behavior is undocumented and may change in future releases (though it may seem unlikely).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With