Re: SQL/JSON: functions

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);
CREATE TABLE

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

=# SET sql_json = jsonb;
SET

=# \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);
CREATE TABLE

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

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

[1] https://docs.oracle.com/en/database/oracle/oracle-database/20/adjsn/json-in-oracle-database.html#GUID-CBEDC779-39A3-43C9-AF38-861AE3FC0AEC

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