jsonpath syntax extensions

From: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: jsonpath syntax extensions
Date: 2020-02-27 15:57:46
Message-ID: e0fe4f7b-da0b-471c-b3da-d8adaf314357@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi, hackers!

Attached patches implement several useful jsonpath syntax extensions.
I already published them two years ago in the original SQL/JSON thread,
but then after creation of separate threads for SQL/JSON functions and
JSON_TABLE I forgot about them.

A brief description of the patches:

1. Introduced new jsonpath modifier 'pg' which is used for enabling
PostgreSQL-specific extensions. This feature was already proposed in the
discussion of jsonpath's like_regex implementation.

2. Added support for raw jbvObject and jbvArray JsonbValues inside jsonpath
engine. Now, jsonpath can operate with JSON arrays and objects only in
jbvBinary form. But with introduction of array and object constructors in
patches #4 and #5 raw in-memory jsonb containers can appear in jsonpath engine.
In some places we can iterate through jbvArrays, in others we need to encode
jbvArrays and jbvObjects into jbvBinay.

3. SQL/JSON sequence construction syntax. A simple comma-separated list can be
used to concatenate single values or sequences into a single resulting sequence.

SELECT jsonb_path_query('[1, 2, 3]', 'pg $[*], 4, 2 + 3');

SELECT jsonb_path_query('{ "a": [1, 2, 3], "b": [4, 5] }',
'pg ($.a[*], $.b[*]) ? (@ % 2 == 1)');

Patches #4-#6 implement ECMAScript-like syntax constructors and accessors:

4. Array construction syntax.
This can also be considered as enclosing a sequence constructor into brackets.

SELECT jsonb_path_query('[1, 2, 3]', 'pg [$[*], 4, 2 + 3]');
[1, 2, 3, 4, 5]

Having this feature, jsonb_path_query_array() becomes somewhat redundant.

5. Object construction syntax. It is useful for constructing derived objects
from the interesting parts of the original object. (But this is not sufficient
to "project" each object in array, item method like '.map()' is needed here.)

SELECT jsonb_path_query('{"b": 2}', 'pg { a : 1, b : $.b, "x y" : $.b + 3 }');
{ "a" : 1, "b": 3, "x y": 5 }

Fields with empty values are simply skipped regardless of lax/strict mode:

SELECT jsonb_path_query('{"a": 1}', 'pg { b : $.b, a : $.a ? (@ > 1) }');

6. Object subscription syntax. This gives us ability to specify what key to
extract on runtime. The syntax is the same as ordinary array subscription

-- non-existent $.x is simply skipped in lax mode
SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$.a, "x", "a"]');

SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$fld]', '{"fld": "b"}');

Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Attachment Content-Type Size
v1-0001-Add-jsonpath-pg-modifier-for-enabling-extensions.patch text/x-patch 13.4 KB
v1-0002-Add-raw-jbvArray-and-jbvObject-support-to-jsonpat.patch text/x-patch 6.3 KB
v1-0003-Add-jsonpath-sequence-constructors.patch text/x-patch 14.3 KB
v1-0004-Add-jsonpath-array-constructors.patch text/x-patch 11.2 KB
v1-0005-Add-jsonpath-object-constructors.patch text/x-patch 14.6 KB
v1-0006-Add-jsonpath-object-subscripting.patch text/x-patch 13.0 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2020-02-27 16:26:26 Re: pg_trigger.tgparentid
Previous Message Robert Haas 2020-02-27 15:52:25 Re: backup manifests