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.