Re: SQL/JSON: functions

On 7/17/20 4:26 PM, Nikita Glukhov wrote:
>
>>> 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.
>> Are these functions in the standard, or are they Oracle extensions? If
>> the latter maybe they belong in an options extension.
> The new SQL type JSON and these functions are in to the new standard SQL/JSON 2.0.
> It is at the proposal stage now, but Oracle 20c has already implemented it.
>
> The document is not publicly available, so Oleg should have sent it to you in a
> private message.
>
>>> 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.
>> What is going to be the effect of that on things like index expressions?
>> This strikes me at first glance as something of a potential footgun, but
>> maybe I'm being overcautious.
> This allows users of 'sql_json=jsonb' to create GIN indexes on SQL type JSON
> (but such creation indexes are still not SQL standard conforming). Maybe I do
> not correctly understand the question or the consequences of type name
> rewriting.
>
> The type names are rewritten on the input at the initial parsing stage and on
> the output in format_type_be(), like it is done for "timestamp with timezone" =>
> timestamptz, integer => int4. Internal representation of query expressions
> remains the same. Affected only representation of JSON types to/from user.
>

I think patches 5 and 6 need to be submitted to the next commitfest,
This is far too much scope creep to be snuck in under the current CF item.

I'll look at patches 1-4.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services