Horton
Menu

Re: SQL/JSON: functions

Andrew Dunstan <andrew.dunstan@2ndquadrant.com>

View thread
From: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
To: Nikita Glukhov <n.gluhov@postgrespro.ru>, 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-18T13:24:11Z
Lists: pgsql-hackers 
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

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.