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!
2 arguments fix just committed
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?
Oh my, now I see there was just the comment author 🙂
It was not obvious with one single comment.