sql: ordering of keys in JSON objects is not identical to Postgres

[dikshant]

Run this in Postgres and CRDB:

create table testtable (id text primary key, jsonb_content jsonb);
insert into testtable (id, jsonb_content) values ('rowid', '{"it is": true, "that": ["this","json"],"is":"in","a weird":"order"}');

The ordering of the results are different:

# CRDB v22.2.3
  id   |                               jsonb_content
-------+---------------------------------------------------------------------------
 rowid | {"a weird": "order", "is": "in", "it is": true, "that": ["this", "json"]}
(1 row)

# Postgres 12.13
  id   |                               jsonb_content
-------+---------------------------------------------------------------------------
 rowid | {"is": "in", "that": ["this", "json"], "it is": true, "a weird": "order"}
(1 row)

This is affecting tools like: https://github.com/udacity/pgverify

Jira issue: CRDB-24156

[ajwerner]

This is tough. Postgres is allowed to order the fields however it wants. I don't know why it orders it the way it does, but the values are technically equal according to postgres. This one is annoying.

select '{"a weird": "order", "is": "in", "it is": true, "that": ["this", "json"]}'::jsonb = '{"it is": true, "that": ["this","json"],"is":"in","a weird":"order"}'::jsonb; is true in postgres and cockroach.

[mgartner]

You can create a recursive UDF in Postgres that mimics our JSON->TEXT cast behavior:

CREATE FUNCTION jsonb_to_text(j JSONB) RETURNS TEXT LANGUAGE SQL AS $$
  SELECT CASE jsonb_typeof(j)
  WHEN 'object' THEN (
    SELECT '{' || (
      SELECT string_agg('"' || key || '": ' || jsonb_to_text(value), ', ')
      FROM (SELECT key, value FROM jsonb_each(j) ORDER BY key) tmp
    ) || '}'
  )
  ELSE j::TEXT
  END
$$;

SELECT jsonb_to_text('{"it is": true, "that": ["this","json"],"is":"in","a weird":"order"}'::JSONB);
-- {"a weird": "order", "is": "in", "it is": true, "that": ["this", "json"]}

SELECT jsonb_to_text('{"it is": true, "that": ["this","json"],"is": {"z": 1, "a": 0}, "a weird":"order"}'::JSONB);
-- {"a weird": "order", "is": {"a": 0, "z": 1}, "it is": true, "that": ["this", "json"]}

You can't create a UDF in CRDB that mimics PG's JSON->TEXT cast because we don't yet support recursive UDFs.

[rafiss]

It seems like udacity/pgverify#18 has worked around this issue, so we will not change anything for now.

[rafiss]

this sort of relates to #95434 - in the sense that PG remembers more about the spacing/ordering inside of a JSON object than CRDB does.

[mgartner]

@rafiss It seems like that's only the case for JSON, not JSONB. Our JSON and JSONB types are identical at the moment, so we can only be consistent with one PG type.

marcus=# SELECT j::JSON, j::JSONB FROM (VALUES ('{"a":    1}')) v(j);
      j      |    j
-------------+----------
 {"a":    1} | {"a": 1}
(1 row)

[miretskiy]

JSON spec says:
An object is an unordered set of name/value pairs. An object begins with {left brace and ends with }right brace. Each name is followed by :colon and the name/value pairs are separated by ,comma.

https://www.json.org/json-en.html

We happen to order our keys (because we need stable sort over golang native maps[] -- but note, new parse json implementation allows one to request unsorted

cockroach/pkg/util/json/config.go

Line 25 in 0d3209c

func WithUnorderedObjectKeys() ParseOption {

)...

There is an argument to be made that applications that rely on any order (PG ordering or CRDB ordering) are somewhat broken. if application requires ordering, it should order keys explicitly. Not sure how hard it is to do -- but @mgartner suggestion seems quite nice.

[mgartner]

There is an argument to be made that applications that rely on any order (PG ordering or CRDB ordering) are somewhat broken.

I agree with this. Key/value pairs in JSON objects have no guaranteed ordering - any observed ordering is an artifact of the specific JSON implementation.

An interesting observation is that Postgres orders keys within a JSON object differently than it compares the keys of two objects:

marcus=# CREATE TABLE t (j JSONB);
CREATE TABLE

marcus=# INSERT INTO t VALUES ('{"a foo": 0, "b": 0}');
INSERT 0 1

marcus=# SELECT j FROM t;
          j
----------------------
 {"b": 0, "a foo": 0}
(1 row)

marcus=# SELECT '{"a foo": 0}'::JSONB < '{"b": 0}'::JSONB;
 ?column?
----------
 t
(1 row)

Within the object, the key "a foo" is considered greater than "b" (assuming the ordering of keys is ascending), but when comparing two objects with keys "a foo" and "b", the "a foo" key is less than "b". For what it's worth, CRDB order keys within JSON objects in the same way that Postgres compares keys between objects.

I think we should close this issue as a "won't fix" or "not a bug".

[rafiss]

I agree - thanks for digging into the details!