PostgreSQL 12, released October 3rd 2019, offers the SQL/JSON Path Language – a way to query and filter data in JSON objects not too dissimilar to how XPath queries XML.

PostgreSQL users have enjoyed native JSON support since version 9.2, released in September 2017. Without a native type, storing JSON as text in tables was somewhat forgivable depending on the application, but there would be no parsing support. Options were limited to bringing the object into another system for parsing or querying.

PostgreSQL has improved support for JSON in subsequent versions offering an increasing number of functions and operators.

Here is a sample JSON object we can use to demonstrate some of the abilities of SQL/JSON.

    {
       "colors":[
          {
             "color":"black",
             "category":"hue",
             "type":"primary",
             "code":{
                "rgba":[
                   255,
                   255,
                   255,
                   1
                ],
                "hex":"#000"
             }
          },
          {
             "color":"white",
             "category":"value",
             "code":{
                "rgba":[
                   0,
                   0,
                   0,
                   1
                ],
                "hex":"#FFF"
             }
          },
          {
             "color":"red",
             "category":"hue",
             "type":"primary",
             "code":{
                "rgba":[
                   255,
                   0,
                   0,
                   1
                ],
                "hex":"#FF0"
             }
          },
          {
             "color":"blue",
             "category":"hue",
             "type":"primary",
             "code":{
                "rgba":[
                   0,
                   0,
                   255,
                   1
                ],
                "hex":"#00F"
             }
          },
          {
             "color":"yellow",
             "category":"hue",
             "type":"primary",
             "code":{
                "rgba":[
                   255,
                   255,
                   0,
                   1
                ],
                "hex":"#FF0"
             }
          },
          {
             "color":"green",
             "category":"hue",
             "type":"secondary",
             "code":{
                "rgba":[
                   0,
                   255,
                   0,
                   1
                ],
                "hex":"#0F0"
             }
          }
       ]
    }

SQL/JSON queries are performed using the jsonb_path_query function.

Selection

Fundamentally, the $ operator gives you a kind of ‘context’ reference for the JSON object.

>> SELECT jsonb_path_query(data, '

returns a single row containing the object:

{"colors": [{"code": {"hex": "#000", "rgba":…

SELECT jsonb_path_query(data, '$.colors') FROM sample_data;

returns a single row containing the colors array:

[{"code": {"hex": "#000", "rgba": [255, 255, 255, 1]}…

We can use the [*] operator to unwind the array elements into individual rows:

    SELECT jsonb_path_query(data, '$.colors[*]') FROM sample_data;
    {"code": {"hex": "#000", "rgba": [255, 255, 255, 1]}…
    {"code": {"hex": "#FFF", "rgba": [0, 0, 0, 1]}…
    {"code": {"hex": "#FF0", "rgba": [255, 0, 0, 1]}…
    {"code": {"hex": "#00F", "rgba": [0, 0, 255, 1]}…
    {"code": {"hex": "#FF0", "rgba": [255, 255, 0, 1]}…
    {"code": {"hex": "#0F0", "rgba": [0, 255, 0, 1]}…

We can further drill down into the properties of the array:

    SELECT jsonb_path_query(data, '$.colors[*].code.hex') FROM sample_data;
    "#000"
    "#FFF"
    "#FF0"
    "#00F"
    "#FF0"
    "#0F0"

Functions

A number of functions are available that we can use within the jsonpath query. For example:

    SELECT jsonb_path_query(data, '$.colors.size()') FROM sample_data;
    6

This query illustrates the type() function. Notice the array index used to target a specific zero-based element.

    SELECT jsonb_path_query(data, '$.colors[2].category.type()') FROM sample_data;
    "string"

Filtering

Filters are the WHERE clause of jsonpath queries. Multiple filters can be used sequentially to further restrict the selected result.

    SELECT jsonb_path_query(data, '$.colors[*] ? (@.type == "primary")') FROM sample_data;

Only colors with a type of primary are returned:

    {"code": {"hex": "#000", "rgba": [255, 255, 255, 1]}…
    {"code": {"hex": "#FF0", "rgba": [255, 0, 0, 1]}…
    {"code": {"hex": "#00F", "rgba": [0, 0, 255, 1]}…
    {"code": {"hex": "#FF0", "rgba": [255, 255, 0, 1]}…

A further selection can be made from the result of the expression:

    SELECT jsonb_path_query(data,
         '$.colors[*] ? (@.type == "primary").code.hex')
    FROM sample_data;
    "#000"
    "#FF0"
    "#00F"
    "#FF0"

Filters can be chained, too. In each case, $ becomes the context variable of the result:

    SELECT jsonb_path_query(data, '$.colors[*] ? (@.type == "primary") ? (@.code.hex == "#000")') FROM sample_data;
    {"code": {"hex": "#000", "rgba": [255, 255, 255, 1]}…

    SELECT jsonb_path_query(data, '$.colors[*] ? (@.type == "primary") ? (@.code.hex == "#000")') FROM sample_data;
    {"code": {"hex": "#000", "rgba": [255, 255, 255, 1]}…

Projection

PostgreSQL’s standard json/jsonb operators can also be applied to the result of jsonpath query.

They’re some what cryptic to learn but do provide critical functions to help you project a value from your result of your query:

->

From a JSON array, the -> operator returns the element at the supplied index if passed an integer, and the object at the specified key if passed a string:

'[{"name": "tom"}, {"name": "dick"}, {"name": "harry"}]'::json -> 3
{"name": "harry"}
'[{"name": "tom", "age": "23", "hobbies": ["football", "cinema"]}'::json -> 'hobbies'
["football", "cinema"]

->>

From a JSON array, the ->> operator returns the text of an element at the supplied index if passed an integer, and the text value at the specified key if passed a string:

'["tom", "dick", "harry"]'::json ->> 0
tom
'[{"name": "tom", "age": "23", "hobbies": ["football", "cinema"]]'::json ->> 'age'
23

#>

The #> operator returns the object at the given path:

'{"person": {"name": "tom", "address": {"zip": "10000"}}}'::json #> '{person, address, zip}';
"10000"

#>>

The #>> operator returns the object at the given path, but as a text result:

'{"person": {"name": "tom", "address": {"zip": "10000"}}}'::json #>> '{person, name}';
tom
'{"person": {"name": "tom", "hobbies": ["cinema", "fishing"], "age": "23", "address": {"city": "New York", "zip": "10000"}}}'::json #>> '{person, hobbies, 1}';
fishing

Hopefully this gives a simple introduction to the JSON support supplied in PostgreSQL < v12 and the new SQL/JSON functions in PostgreSQL.