Horton
Menu

Re: SQL/JSON: functions

Nikita Glukhov <n.gluhov@postgrespro.ru>

View thread
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>, pgsql-hackers@postgresql.org
Cc: Justin Pryzby <pryzby@telsasoft.com>, Alexander Korotkov <a.korotkov@postgrespro.ru>, Pavel Stehule <pavel.stehule@gmail.com>, Erik Rijkers <er@xs4all.nl>, Alvaro Herrera <alvherre@2ndquadrant.com>, Thomas Munro <thomas.munro@gmail.com>, Andrew Alsup <bluesbreaker@gmail.com>, Andres Freund <andres@anarazel.de>, Dmitry Dolgov <9erthalion6@gmail.com>, Oleg Bartunov <obartunov@postgrespro.ru>, Michael Paquier <michael@paquier.xyz>, Simon Riggs <simon@2ndquadrant.com>
Date: 2020-07-17T20:26:04Z
Lists: pgsql-hackers

Attachments

Attached 50th version of the patches. Only the documentation was changed
since the previous version.

On 15.07.2020 14:50, Andrew Dunstan wrote:

> On 7/14/20 9:47 PM, Nikita Glukhov wrote:
>
> On 15.07.2020 00:09, Andrew Dunstan wrote:
>>> On 7/14/20 1:00 PM, Andrew Dunstan wrote:
>>>> 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.
> Here's the relevant commit message that explains the policy:
>
> commit 47046763c3
> Author: Tom Lane <tgl@sss.pgh.pa.us>
> Date:   Mon May 4 13:48:30 2020 -0400
>
>      Doc: standardize markup a bit more.
>     
>      We had a mishmash of <replaceable>, <replaceable class="parameter">,
>      and <parameter> markup for operator/function arguments.  Use <parameter>
>      consistently for things that are in fact names of parameters (including
>      OUT parameters), reserving <replaceable> for things that aren't.  The
>      latter class includes some made-up-by-the-docs type class names, like
>      "numeric_type", as well as placeholders for arguments that don't have
>      well-defined types.  Possibly we could do better with those categories
>      as well, but for the moment I'm content not to have parameter names
>      marked up in different ways in different places.
>>> 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.

Ok, I replaced some <replaceable> with <parameter> in SQL/JSON constructors.
Also I replaced all '[]' with <optional>.


>> 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.

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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. JSON_TABLE: Add support for NESTED paths and columns

  2. Add basic JSON_TABLE() functionality

  3. Add SQL/JSON query functions

  4. Add soft error handling to some expression nodes

  5. Adjust populate_record_field() to handle errors softly

  6. Refactor code used by jsonpath executor to fetch variables

  7. Add more SQL/JSON constructor functions

  8. SQL/JSON: support the IS JSON predicate

  9. SQL/JSON: add standard JSON constructor functions

  10. Improve readability of SQL/JSON documentation.

  11. Documentation for SQL/JSON features

  12. RETURNING clause for JSON() and JSON_SCALAR()

  13. SQL JSON functions

  14. SQL/JSON query functions

  15. IS JSON predicate

  16. SQL/JSON constructors

  17. Common SQL/JSON clauses

  18. Doc: remove obsolete entries in table of BRIN strategy numbers.

  19. Doc: standardize markup a bit more.

  20. Make pg_regress.c unset PGDATABASE during make installcheck.

  21. Fix handling of GENERATED columns in CREATE TABLE LIKE INCLUDING DEFAULTS.