时间戳

首页 > 解决方案 > Is jsonb conversion to text deterministic?

postgresql - 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!

标签： postgresqljsonb

解决方案

由于JSON 对象定义，正式顺序不是确定性的：

对象是一组无序的名称/值对。

实际上，对象似乎是按键的长度然后按字母顺序排序的：

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)

请注意，此行为未记录在案，并且可能会在将来的版本中更改（尽管看起来不太可能）。

推荐阅读