PostgreSQL: compare jsons [duplicate]

Question

As known, at the moment PostgreSQL has no method to compare two json values. The comparison like json = json doesn't work. But what about casting json to text before?

Then

select ('{"x":"a", "y":"b"}')::json::text = 
('{"x":"a", "y":"b"}')::json::text

returns true

while

select ('{"x":"a", "y":"b"}')::json::text = 
('{"x":"a", "y":"d"}')::json::text

returns false

I tried several variants with more complex objects and it works as expected.

Are there any gotchas in this solution?

UPDATE:

The compatibility with v9.3 is needed

Answer 1

Yes there are multiple problem with your approach (i.e. converting to text). Consider the following example

select ('{"x":"a", "y":"b"}')::json::text = ('{"y":"b", "x":"a"}')::json::text;

This is like your first example example, except that I flipped the order of the x and y keys for the second object, and now it returns false, even thought the objects are equal.

Another issue is that json preserves white space, so

select ('{"x":"a", "y":"b"}')::json::text = ('{ "x":"a", "y":"b"}')::json::text;

returns false just because I added a space before the x in the second object.

A solution that works with v9.3 is to use the json_each_text function to expand the two JSON objects into tables, and then compare the two tables, e.g. like so:

SELECT NOT exists(
    SELECT
    FROM json_each_text(('{"x":"a", "y":"b"}')::json) t1
         FULL OUTER JOIN json_each_text(('{"y":"b", "x":"a"}')::json) t2 USING (key)
    WHERE t1.value<>t2.value OR t1.key IS NULL OR t2.key IS NULL
)

Note that this only works if the two JSON values are objects where for each key, the values are strings.

The key is in the query inside the exists: In that query we match all keys from the first JSON objects with the corresponding keys in the second JSON object. Then we keep only the rows that correspond to one of the following two cases:

• a key exists in both JSON objects but the corresponding values are different
• a key exists only in one of the two JSON objects and not the other

These are the only cases that "witness" the inequality of the two objects, hence we wrap everything with a NOT exists(...), i.e. the objects are equal if we didn't find any witnesses of inequality.

If you need to support other types of JSON values (e.g. arrays, nested objects, etc), you can write a plpgsql function based on the above idea.

Answer 2

Most notably A @> B AND B @> A will signify TRUE if they are both equal JSONB objects.

However, be careful when assuming that it works for all kinds of JSONB values, as demonstrated with the following query:

select
    old,
    new,
    NOT(old @> new AND new @> old) as changed
from (
    values
        (
        '{"a":"1", "b":"2", "c": {"d": 3}}'::jsonb,
        '{"b":"2", "a":"1", "c": {"d": 3, "e": 4}}'::jsonb
        ),
        (
        '{"a":"1", "b":"2", "c": {"d": 3, "e": 4}}'::jsonb,
        '{"b":"2", "a":"1", "c": {"d": 3}}'::jsonb
        ),
        (
        '[1, 2, 3]'::jsonb,
        '[3, 2, 1]'::jsonb
        ),
        (
        '{"a": 1, "b": 2}'::jsonb,
        '{"b":2, "a":1}'::jsonb
        ),
        (
        '{"a":[1, 2, 3]}'::jsonb,
        '{"b":[3, 2, 1]}'::jsonb
        )
) as t (old, new)

Problems with this approach are that JSONB arrays are not compared correctly, as in JSON [1, 2, 3] != [3, 2, 1] but Postgres returns TRUE nevertheless.

A correct solution will recursively iterate through the contents of the json and comparing arrays and objects differently. I have quickly built a set of functions that accomplishes just that.

Use them like SELECT jsonb_eql('[1, 2, 3]'::jsonb, '[3, 2, 1]'::jsonb) (the result is FALSE).

CREATE OR REPLACE FUNCTION jsonb_eql (a JSONB, b JSONB) RETURNS BOOLEAN AS $$
DECLARE
BEGIN
  IF (jsonb_typeof(a) != jsonb_typeof(b)) THEN
    RETURN FALSE;
  ELSE
    IF (jsonb_typeof(a) = 'object') THEN
      RETURN jsonb_object_eql(a, b);
    ELSIF (jsonb_typeof(a) = 'array') THEN
      RETURN jsonb_array_eql(a, b);
    ELSIF (COALESCE(jsonb_typeof(a), 'null') = 'null') THEN
      RETURN COALESCE(a, 'null'::jsonb) = 'null'::jsonb AND COALESCE(b, 'null'::jsonb) = 'null'::jsonb;
    ELSE
      RETURN coalesce(a = b, FALSE);
    END IF;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION jsonb_object_eql (a JSONB, b JSONB) RETURNS BOOLEAN AS $$
DECLARE
   _key_a text;
   _val_a jsonb;
   _key_b text;
   _val_b jsonb;
BEGIN
  IF (jsonb_typeof(a) != jsonb_typeof(b)) THEN
    RETURN FALSE;
  ELSIF (jsonb_typeof(a) != 'object') THEN
    RETURN jsonb_eql(a, b);
  ELSE
    FOR _key_a, _val_a, _key_b, _val_b IN
      SELECT t1.key, t1.value, t2.key, t2.value FROM jsonb_each(a) t1
      LEFT OUTER JOIN (
        SELECT * FROM jsonb_each(b)
      ) t2 ON (t1.key = t2.key)
    LOOP
      IF (_key_a != _key_b) THEN
        RETURN FALSE;
      ELSE
        RETURN jsonb_eql(_val_a, _val_b);
      END IF;
    END LOOP;
    RETURN a = b;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION jsonb_array_eql (a JSONB, b JSONB) RETURNS BOOLEAN AS $$
DECLARE
   _val_a jsonb;
   _val_b jsonb;
BEGIN
  IF (jsonb_typeof(a) != jsonb_typeof(b)) THEN
    RETURN FALSE;
  ELSIF (jsonb_typeof(a) != 'array') THEN
    RETURN jsonb_eql(a, b);
  ELSE
    FOR _val_a, _val_b IN
      SELECT jsonb_array_elements(a), jsonb_array_elements(b)
    LOOP
      IF (NOT(jsonb_eql(_val_a, _val_b))) THEN
        RETURN FALSE;
      END IF;
    END LOOP;
    RETURN TRUE;
  END IF;
END;
$$ LANGUAGE plpgsql;

Answer 3

You can also use the @> operator. Let's say you have A and B, both JSONB objects, so A = B if:

A @> B AND A <@ B

Read more here: https://www.postgresql.org/docs/current/functions-json.html