Waiting for PostgreSQL 12 – Partial implementation of SQL/JSON path language

On 16th of March 2019, Alexander Korotkov committed patch:

Partial implementation of SQL/JSON path language
 
SQL 2016 standards among other things contains set of SQL/JSON features for
JSON processing inside of relational database.  The core of SQL/JSON is JSON
path language, allowing access parts of JSON documents and make computations
over them.  This commit implements partial support JSON path language as
separate datatype called "jsonpath".  The implementation is partial because
it's lacking datetime support and suppression of numeric errors.  Missing
features will be added later by separate commits.
 
Support of SQL/JSON features requires implementation of separate nodes, and it
will be considered in subsequent patches.  This commit includes following
set of plain functions, allowing to execute jsonpath over jsonb values:
 
 * jsonb_path_exists(jsonb, jsonpath[, jsonb, bool]),
 * jsonb_path_match(jsonb, jsonpath[, jsonb, bool]),
 * jsonb_path_query(jsonb, jsonpath[, jsonb, bool]),
 * jsonb_path_query_array(jsonb, jsonpath[, jsonb, bool]).
 * jsonb_path_query_first(jsonb, jsonpath[, jsonb, bool]).
 
This commit also implements "jsonb @? jsonpath" and "jsonb @@ jsonpath", which
are wrappers over jsonpath_exists(jsonb, jsonpath) and jsonpath_predicate(jsonb,
jsonpath) correspondingly.  These operators will have an index support
(implemented in subsequent patches).
 
Catversion bumped, to add new functions and operators.
 
Code was written by Nikita Glukhov and Teodor Sigaev, revised by me.
Documentation was written by Oleg Bartunov and Liudmila Mantrova.  The work
was inspired by Oleg Bartunov.
 
Discussion: https://postgr.es/m/-b497-f39a-923d-%402ndQuadrant.com
Author: Nikita Glukhov, Teodor Sigaev, Alexander Korotkov, Oleg Bartunov, Liudmila Mantrova

This is pretty cool.

Let's consider this json:

=$ DROP TABLE test;
DROP TABLE
 
=$ CREATE TABLE test (x jsonb);
CREATE TABLE
 
=$ INSERT INTO test (x) VALUES ('{"id": 123, "tags": {"name": "a", "other": "b"}, "sizes": [1,5,10]}');
INSERT 0 1
 
=$ SELECT jsonb_pretty(x) FROM test;
     jsonb_pretty     
----------------------
 {                   +
     "id": 123,      +
     "tags": {       +
         "name": "a",+
         "other": "b"+
     },              +
     "sizes": [      +
         1,          +
         5,          +
         10          +
     ]               +
 }
(1 ROW)

Full description of every bit of jsonpath syntax is in the doc, so let's just show some quick examples:

=$ SELECT jsonb_path_query(x, '$.*') FROM test;
      jsonb_path_query       
-----------------------------
 123
 {"name": "a", "other": "b"}
 [1, 5, 10]
(3 ROWS)
 
=$ SELECT jsonb_path_query(x, '$.sizes[*]') FROM test;
 jsonb_path_query 
------------------
 1
 5
 10
(3 ROWS)
 
=$ SELECT jsonb_path_query(x, '$.**.name') FROM test;
 jsonb_path_query 
------------------
 "a"
(1 ROW)
 
=$ SELECT jsonb_path_query(x, '$.** ? (@.name == "x")') FROM test;
 jsonb_path_query 
------------------
(0 ROWS)
 
=$ SELECT jsonb_path_query(x, '$.** ? (@.name == "a")') FROM test;
      jsonb_path_query       
-----------------------------
 {"name": "a", "other": "b"}
(1 ROW)
 
=$ SELECT jsonb_path_query(x, '$.sizes[1 to last]') FROM test;
 jsonb_path_query 
------------------
 5
 10
(2 ROWS)

To use this new datatype (jsonpath), we have following functions:

jsonb_path_exists

There are supposedly two version of the function:

  • jsonb_path_exists(jsonb, jsonpath) RETURNS boolean
  • jsonb_path_exists(target jsonb, path jsonpath, vars jsonb DEFAULT ‘{}'::jsonb, silent boolean DEFAULT false) RETURNS boolean

But I wasn't able to use the two-argument version. Whatever I did, I got:

=$ SELECT jsonb_path_exists(x, '$.id') FROM test;
ERROR:  FUNCTION jsonb_path_exists(jsonb, UNKNOWN) IS NOT UNIQUE
LINE 1: SELECT jsonb_path_exists(x, '$.id') FROM test;
               ^
HINT:  Could NOT choose a best candidate FUNCTION. You might need TO ADD explicit TYPE casts.

But, providing any of the vars/silent args solves the problem:

=$ SELECT jsonb_path_exists(x, '$.id', vars => '{}') FROM test;
 jsonb_path_exists 
-------------------
 t
(1 ROW)
 
=$ SELECT jsonb_path_exists(x, '$.id', silent => TRUE) FROM test;
 jsonb_path_exists 
-------------------
 t
(1 ROW)

jsonb_path_match

Also two versions:

  • jsonb_path_match(jsonb, jsonpath) RETURNS boolean
  • jsonb_path_match(target jsonb, path jsonpath, vars jsonb DEFAULT ‘{}'::jsonb, silent boolean DEFAULT false) RETURNS boolean

and as previously, I can't run the two argument version:

=$ SELECT jsonb_path_match(x, '$.id') FROM test;
ERROR:  FUNCTION jsonb_path_match(jsonb, UNKNOWN) IS NOT UNIQUE
LINE 1: SELECT jsonb_path_match(x, '$.id') FROM test;
               ^
HINT:  Could NOT choose a best candidate FUNCTION. You might need TO ADD explicit TYPE casts.

Using it is trickier:

=$ SELECT jsonb_path_match(x, '$.id', vars => '{}') FROM test;
 jsonb_path_match 
------------------
 [NULL]
(1 ROW)
 
=$ SELECT jsonb_path_match(x, '$.id', silent => TRUE) FROM test;
 jsonb_path_match 
------------------
 [NULL]
(1 ROW)

But you can use it with a filter expression:

=$ SELECT jsonb_path_match(x, 'exists($.id)', silent => TRUE) FROM test;
 jsonb_path_match 
------------------
 t
(1 ROW)

of course, with exists it's basically just copy of jsonb_path_exists, but there are many more filters available.

jsonb_path_query(target jsonb, path jsonpath, vars jsonb DEFAULT ‘{}'::jsonb, silent boolean DEFAULT false) RETURNS SETOF jsonb

This extracts each element of input json into separate value (record). Like this:

=$ SELECT jsonb_path_query(x, '$.*') FROM test;
      jsonb_path_query       
-----------------------------
 123
 {"name": "a", "other": "b"}
 [1, 5, 10]
(3 ROWS)

jsonb_path_query_array(target jsonb, path jsonpath, vars jsonb DEFAULT ‘{}'::jsonb, silent boolean DEFAULT false) RETURNS jsonb

Just like jsonb_path_query, but instead of returning each match in separate row, it generates json array with results:

=$ SELECT jsonb_path_query_array(x, '$.*') FROM test;
             jsonb_path_query_array             
------------------------------------------------
 [123, {"name": "a", "other": "b"}, [1, 5, 10]]
(1 ROW)

jsonb_path_query_first(target jsonb, path jsonpath, vars jsonb DEFAULT ‘{}'::jsonb, silent boolean DEFAULT false) RETURNS jsonb

As name suggests, it simply returns just the first match:

=$ SELECT jsonb_path_query_first(x, '$.*') FROM test;
 jsonb_path_query_first 
------------------------
 123
(1 ROW)

This is just a scratch on the surface of jsonpath, but if you're interested in more details – docs are there for you 🙂

Jsonpath looks absolutely amazing (and will be even more cool when it will get indexing support). Thanks to everyone involved, great job!

3 thoughts on “Waiting for PostgreSQL 12 – Partial implementation of SQL/JSON path language”

  1. Who is the post author?
    Oleg Bartunov, one of jsonpath (and most other json stuff) developers, failed to run two-argument functions? And didn’t try to cast text::jsonpath?

  2. Oh my, now I see there was just the comment author 🙂
    It was not obvious with one single comment.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.