Re: SQL/JSON: functions

From: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
To: Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, pgsql-hackers(at)postgresql(dot)org, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Erik Rijkers <er(at)xs4all(dot)nl>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Andrew Alsup <bluesbreaker(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Dmitry Dolgov <9erthalion6(at)gmail(dot)com>, Oleg Bartunov <obartunov(at)postgrespro(dot)ru>, Michael Paquier <michael(at)paquier(dot)xyz>, Simon Riggs <simon(at)2ndquadrant(dot)com>
Subject: Re: SQL/JSON: functions
Date: 2020-07-15 01:47:21
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Attached 49th version of the patches with two new patches #5 and #6.

On 15.07.2020 00:09, Andrew Dunstan wrote:
> On 7/14/20 1:00 PM, Andrew Dunstan wrote:
>> On 7/5/20 1:29 PM, Justin Pryzby wrote:
>>> On Mon, Mar 23, 2020 at 08:28:52PM +0300, Nikita Glukhov wrote:
>>>> Attached 47th version of the patches.
>>> The patch checker/cfbot says this doesn't apply ; could you send a rebasified
>>> version ?
>> To keep things moving, I've rebased these patches. However, 1) the docs
>> patches use <replaceble class="parameter"> in many cases where they
>> should now just use <parameter>

I haven't touched <replaceable class="parameter"> yet, because I'm not sure
if <replaceable> or <parameter> is correct here at all.

> Turns out these patches also need to get the message on the new way of
> writing entries in func.sgml - I'll publish some updates on that in the
> next day or so so that "make doc" will succeed.

I can do it by myself, but I just need to understand what to fix and how.

>> and b) patch 4 fails when run under force_parallel=regress.

Fixed parallel-safety check for RETURNING clause of JSON_EXISTS().

On 05.04.2020 19:50, Alexander Korotkov wrote:
> 1) Uniqueness checks using JsonbUniqueCheckContext and
> JsonUniqueCheckContext have quadratic complexity over number of keys.
> That doesn't look good especially for jsonb, which anyway sorts object
> keys before object serialization.
> 2) We have two uniqueness checks for json type, which use
> JsonbUniqueCheckContext and JsonUniqueState. JsonUniqueState uses
> stack of hashes, while JsonbUniqueCheckContext have just plain array
> of keys. I think we can make JsonUniqueState use single hash, where
> object identifies would be part of hash key. And we should replace
> JsonbUniqueCheckContext with JsonUniqueState. That would eliminate
> extra entities and provide reasonable complexity for cases, which now
> use JsonbUniqueCheckContext.

Unique checks were refactored as Alexander proposed.

> 3) New SQL/JSON clauses don't use timezone and considered as immutable
> assuming all the children are immutable. Immutability is good, but
> ignoring timezone in all the cases is plain wrong. The first thing we
> can do is to use timezone and make SQL/JSON clauses stable. But that
> limits their usage in functional and partial indexes. I see couple of
> things we can do next (one of them or probably both).
> 3.1) Provide user a way so specify that we should ignore timezone in
> particular case (IGNORE TIMEZONE clause or something like that). Then
> SQL/JSON clause will be considered as immutable.
> 3.2) Automatically detect whether jsonpath might use timezone. If
> jsonpath doesn't use .datetime() method, it doesn't need timezone for
> sure. Also, from the datetime format specifiers we can get that we
> don't compare non-timezoned values to timezoned values. So, if we
> detect this jsonpath never uses timezone, we can consider SQL/JSON
> clause as immutable.

Implemented second variant with automatic detection.

I also tried to add explicit IGNORE TIMEZONE / IMMUTABLE clauses, but all of
them lead to shift/reduce conflicts that seem not easy to resolve.

Patch #5 implements functions for new JSON type that is expected to appear in
the upcoming SQL/JSON standard:

- JSON() is for constructing JSON typed values from JSON text.
It is almost equivalent to text::json[b] cast, except that it has additional
ability to specify WITH UNIQUE KEYS constraint.

- JSON_SCALAR() is for constructing JSON typed values from SQL scalars.
It is equivalent to to_json[b]().

- JSON_SERIALIZE() is for serializing JSON typed values to character strings.
It is almost equivalent to json[b]::character_type cast, but it also
supports output to bytea.

Upcoming Oracle 20c will have JSON datatype and these functions [1], so we
decided also to implement them for compatibility, despite that they do not make
sense for real PG users.

Patch #6 allows the user to use PG jsonb type as an effective implementation of
SQL JSON type. By explicitly setting GUC sql_json = jsonb, JSON will be mapped
to jsonb, and JSON TEXT (may be named named differently) will be mapped to json.

This seems to be a hack, but we failed to propose something more simpler.

Example of usage GUC sql_json:

=# CREATE TABLE test1 (js json, jb jsonb, jt json text);

=# \d test1
Table "public.test1"
Column | Type | Collation | Nullable | Default
js | json | | |
jb | jsonb | | |
jt | json | | |

=# SET sql_json = jsonb;

=# \d test1
Table "public.test1"
Column | Type | Collation | Nullable | Default
js | json text | | |
jb | json | | |
jt | json text | | |

=# CREATE TABLE test2 (js json, jb jsonb, jt json text);

=# \d test2
Table "public.test2"
Column | Type | Collation | Nullable | Default
js | json | | |
jb | json | | |
jt | json text | | |

=# SET sql_json = json;
=# \d test2
Table "public.test2"
Column | Type | Collation | Nullable | Default
js | jsonb | | |
jb | jsonb | | |
jt | json | | |


Nikita Glukhov
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
0001-Common-SQL-JSON-clauses-v49.patch.gz application/gzip 8.0 KB
0002-SQL-JSON-constructors-v49.patch.gz application/gzip 33.4 KB
0003-IS-JSON-predicate-v49.patch.gz application/gzip 11.7 KB
0004-SQL-JSON-query-functions-v49.patch.gz application/gzip 39.3 KB
0005-SQL-JSON-functions-for-JSON-type-v49.patch.gz application/gzip 12.6 KB
0006-GUC-sql_json-v49.patch.gz application/gzip 4.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-07-15 02:28:48 Re: Binary support for pgoutput plugin
Previous Message Andres Freund 2020-07-15 01:41:13 Re: Binary support for pgoutput plugin