PostgreSQL: How to Compare JSON Arrays

Often in PostgreSQL you may want to compare two JSON arrays.

You can use the following basic syntax to do so:

SELECT *,
      game1 @> game2 AND game2 <@ game1 AS equal_games
FROM athletes;

This particular example will check if the two JSON arrays in the fields named game1 and game2 are equal and return either true or false as a result.

The following example shows how to use this syntax in practice.

Example: How to Compare JSON Arrays in PostgreSQL

Suppose that we create the following table named athletes that contains information about two basketball games player by various athletes:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  game1 JSONB,
  game2 JSONB
);

-- add rows to the table 
INSERT INTO athletes VALUES (1, '{"win":"yes", "p":"no"}', '{"win":"yes", "p":"no"}');
INSERT INTO athletes VALUES (2, '{"win":"no", "p":"no"}', '{"win":"no", "p":"yes"}');
INSERT INTO athletes VALUES (3, '{"win":"yes", "p":"yes"}', '{"win":"yes", "p":"yes"}');
INSERT INTO athletes VALUES (4, '{"win":"yes", "p":"no"}', '{"win":"no", "p":"no"}');

-- view all rows in table
SELECT * FROM athletes;

 id |           game1            |           game2            
----+----------------------------+----------------------------
  1 | {"p": "no", "win": "yes"}  | {"p": "no", "win": "yes"}
  2 | {"p": "no", "win": "no"}   | {"p": "yes", "win": "no"}
  3 | {"p": "yes", "win": "yes"} | {"p": "yes", "win": "yes"}
  4 | {"p": "no", "win": "yes"}  | {"p": "no", "win": "no"}

The game1 and game2 columns contain the following information in JSON arrays:

• p: Whether or not the game was a playoff game
• win: Whether or not the athlete was on the winning team

Suppose that we would like to compare the JSON arrays in game1 and game2 in each row and check if they’re equal.

We can use the following syntax to do so:

SELECT *,
      game1 @> game2 AND game2 <@ game1 AS equal_games
FROM athletes;

 id |           game1            |           game2            | equal_games 
----+----------------------------+----------------------------+-------------
  1 | {"p": "no", "win": "yes"}  | {"p": "no", "win": "yes"}  | t
  2 | {"p": "no", "win": "no"}   | {"p": "yes", "win": "no"}  | f
  3 | {"p": "yes", "win": "yes"} | {"p": "yes", "win": "yes"} | t
  4 | {"p": "no", "win": "yes"}  | {"p": "no", "win": "no"}   | f

Notice that the resulting table contains a new column named equal_games that returns either t (true) or f (false) to indicate whether or not the JSON arrays from the game1 and game2 columns are equal.

You can use similar syntax to compare JSON arrays in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Filter Table by JSONB Field
PostgreSQL: How to Get Last Item in JSON Array
PostgreSQL: How to Remove Element from JSONB Array