Is jsonb conversion to text deterministic?

user_8786602 0
Viewed 145

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!

jsonb postgresql
1 Answers

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).

user_19957380
Related