Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is jsonb conversion to text deterministic?

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!

like image 342
Wellspring Avatar asked Apr 09 '26 23:04

Wellspring


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

like image 104
klin Avatar answered Apr 12 '26 13:04

klin