Re: jsonpath

From: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
To: Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
Cc: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Oleg Bartunov <obartunov(at)postgrespro(dot)ru>, Michael Paquier <michael(at)paquier(dot)xyz>, Stas Kelvich <s(dot)kelvich(at)postgrespro(dot)ru>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, David Steele <david(at)pgmasters(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Subject: Re: jsonpath
Date: 2019-03-01 00:36:49
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

Attached 34th version of the patches.

1. Partial jsonpath support:
- Fixed copying of jsonb with vars jsonb_path_query() into SRF context
- Fixed error message for jsonpath vars
- Fixed file-level comment in jsonpath.c

2. Suppression of numeric errors:
Now error handling is done without PG_TRY/PG_CATCH using a bunch of internal
numeric functions with 'bool *error' flag.

3. Datetime support:
Problems with timzeones still exist. Comparison of tz types with
non-tz types is simply disallowed. Default integer timezone offset (not
abbreviation) can be specified with the second .datetime() argument.
Error handling also is done using internal functions.

4. Json type support:
Json support was completely refactored since v23: double compilation with
function redefinitions was replaced with passing 'isJsonb' flag to low-level
json/jsonb access functions.
Also major refactoring with introduction of struct JsonItem was made.
JsonItem is used in executor instead of raw JsonbValue. This helps to avoid
extending of JsonbValue for datetime types and also other numeric types
(integers and floats) required by standard.

5. GIN support:
Nothing was changed since v23.

Patch 1 is what we are going to commit in PG12.
Patches 2 and 3 add code that was removed in the previous v33 version.

On 24.02.2019 15:34, Alexander Korotkov wrote:

> On Sun, Feb 24, 2019 at 2:44 PM Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>> On 2/24/19 10:03 AM, Alexander Korotkov wrote:
>>> Attached is revised version of jsonpath. Assuming that jsonpath have
>>> problem places, I decided to propose partial implementation.
>>> Following functionality was cut from jsonpath:
>>> 1) Support of datetime datatypes. Besides error suppression, this
>>> functionality have troubles with timezones. According to standard we
>>> should support timezones in jsonpath expressions. But that would
>>> prevent jsonpath functions from being immutable, that in turn limits
>>> their usage in expression indexes.
>> Assuming we get the patch committed without the datetime stuff now, what
>> does that mean for the future? Does that mean we'll be unable to extend
>> it to support datetime in the future, or what? If we define the jsonpath
>> functions as immutable now, people may create indexes - which means we
>> won't be able to downgrade it to stable later.
>> So, what's the plan here? The only thing I can think of is having two
>> sets of functions - an immutable one, prohibiting datetime expressions,
>> and stable that can't be used for indexes etc.
> Reasonable question. As I understand, not datetime support itself
> making jsonpath functions not immutable, but implicit cast happening
> during comparison timestamp vs. timestamptz (and time vs. timetz).
> So, in future immutable functions can have limited support of
> datetime, where comparison of non-tz vs. tz types is restricted. And
> stable versions of functions (for instance, with '_tz' prefix) with
> full datetime support.

I can also offset to explicitly pass timezone info into jsonpath function using
the special user dataype encapsulating struct pg_tz.

But simple integer timezone offset can be passed now using jsonpath variables
(standard says only about integer timezone offsets; also it requires presence
of timezone offset it in the input string if the format string contain timezone

=# SELECT jsonb_path_query(
'"28-02-2019 12:34"',
'$.datetime("DD-MM-YYYY HH24:MI TZH", $tz)',
jsonb_build_object('tz', EXTRACT(TIMEZONE FROM now()))

(1 row)

>>> 2) Suppression of numeric errors. I will post it as a separate patch.
>>> Pushing this even this partial implementation of jsonpath to PG 12 is
>>> still very useful. Also it will simplify a lot pushing other parts of
>>> SQL/JSON to future releases.
>> +1 to push at least partial (but still useful) subset, instead of just
>> bumping the patch to PG13
See patch #2.
> Thank you for support!
Nikita Glukhov
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
0001-Partial-implementation-of-SQL-JSON-path-language-v34.patch text/x-patch 260.9 KB
0002-Suppression-of-numeric-errors-in-jsonpath-v34.patch text/x-patch 21.4 KB
0003-Implementation-of-datetime-in-jsonpath-v34.patch text/x-patch 121.8 KB
0004-Jsonpath-support-for-json-v34.patch text/x-patch 192.5 KB
0005-Jsonpath-GIN-support-v34.patch text/x-patch 47.0 KB

In response to

  • Re: jsonpath at 2019-02-24 12:34:22 from Alexander Korotkov


Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2019-03-01 00:49:47 Re: jsonpath
Previous Message Ramanarayana 2019-03-01 00:36:17 Re: XML/XPath issues: text/CDATA in XMLTABLE, XPath evaluated with wrong context