Unique JSON Features in PostgreSQL

Suport for JSON is ubiquitous in modern relational database. Since the introduction of JSON support in th SQL:2016 standard, accessing (nested) JSON values is very similar across database products using SQL/JSON path expressions.

PostgreSQL was the first relational database to support JSON and corresponding JSON functions. With version 12 it also supports SQL/JSON path.

For certain types of conditions, the PostgreSQL specific operators and functions are more powerful than the SQL/JSON path functions.

Array containment

Especially the contains operator @> has capabilities not found in other database products.

Take the following sample table and data:

create table some_table (id integer primary key, data jsonb);
insert into some_table (id, data) 
values 
(1, '{"selection":[{"item": 1}, {"item": 4}, {"item": 5}]}'),
(2, '{"selection":[{"item": 1}, {"item": 2}, {"item": 3}, {"item": 7}]}');

Querying JSON value for rows that contain a specific item can easily be done using SQL/JSON path expressions with standard SQL.

select *
from some_table
where json_exists(data, '$.selection[*] ? (@.item == 1)');

For Postgres you have to use jsonb_path_exists() instead of json_exists(), but otherwise it would be identical.

However, things get more complicated when trying to check for multiple items in the array.

With Postgres’ @> operator this is very easy:

select *
from some_table
where data @> '{"selection": [{"item": 1}, {"item": 3}]}';

This will return only rows where the “selection” array contains both items.

The order of the elements in the parameter does not matter. Using @> '{"selection": [{"item": 3}, {"item": 2]}' would return the result.

With standard SQL, this would need to be written as:

select *
from some_table
where json_exists(data, '$.selection[*] ? (@.item == 1)')
  and json_exists(data, '$.selection[*] ? (@.item == 3)');

Obviously this is quite cumbersome with more than just two elements.

This also works with “simple arrays” that do not contain objects, but scalar values.

select *
from some_table
where data @> '[1,2,3]';

The above will return rows with the JSON content [1,2,3,4,5,6] just as well as those with e.g. [7,8,1,10,3,2].

Again, with SQL/JSON path, the “iteration” over the array needs to happen three times:

select *
from some_table
where json_exists(data, '$[*] ? (@ == 1)')
  and json_exists(data, '$[*] ? (@ == 2)')
  and json_exists(data, '$[*] ? (@ == 3)');

Key/Value containment

The @> operator can also deal with key/value pairs the same way,

The query

select *
from some_table
where some_column @> '{"key_one": 1, "key_two": 42, "key_three": "Heart of Gold"}';

will only return rows where all three keys with the corresponding values are present in the column’s value.

Although it can be done using a single json_exists() condition with SQL/JSON path, it’s still a bit more clumsy:

select *
from some_table
where json_exists(some_column, '$ ? (@.key_one == 1 && @.key_two == 42 && @.key_three == "Heart of Gold")');

Performance

The @> operator can make use of a GIN index that is defined on the complete column. I am not aware of any other database where using json_exists() to filter on nested arrays could benefit from an index.

Conclusion

Although support of SQL/JSON path is a welcome addition to the feature set of PostgreSQL, the traditional JSON operators are more powerful and easier to use.