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');
jsonb_path_query
------------------
1
2
3
4
5
SELECT jsonb_path_query('{ "a": [1, 2, 3], "b": [4, 5] }',
'pg ($.a[*], $.b[*]) ? (@ % 2 == 1)');
jsonb_path_query
------------------
1
3
5
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]');
jsonb_path_query
------------------
[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 }');
jsonb_path_query
-------------------------------
{ "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) }');
jsonb_path_query
------------------
{}
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
syntax.
-- non-existent $.x is simply skipped in lax mode
SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$.a, "x", "a"]');
jsonb_path_query
------------------
"c"
"b"
SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$fld]', '{"fld": "b"}');
jsonb_path_query
------------------
"c"
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
