Re: SQL/JSON revisited

Hi hackers!

The latest SQL standard contains dot notation for JSON. Are there any plans
to include it into Pg 16?
Or maybe we should start a separate thread for it?

Thanks!


On Tue, Apr 4, 2023 at 3:36 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

> On 2023-Apr-04, Amit Langote wrote:
>
> > On Tue, Apr 4, 2023 at 2:16 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
> wrote:
>
> > > - the gram.y solution to the "ON ERROR/ON EMPTY" clauses is quite ugly.
> > >   I think we could make that stuff use something similar to
> > >   ConstraintAttributeSpec with an accompanying post-processing
> function.
> > >   That would reduce the number of ad-hoc hacks, which seem excessive.
> >
> > Do you mean the solution involving the JsonBehavior node?
>
> Right.  It has spilled as the separate on_behavior struct in the core
> parser %union in addition to the raw jsbehavior, which is something
> we've gone 30 years without having, and I don't see why we should start
> now.
>
> This stuff is terrible:
>
> json_exists_error_clause_opt:
>             json_exists_error_behavior ON ERROR_P       { $$ = $1; }
>             | /* EMPTY */                               { $$ = NULL; }
>         ;
>
> json_exists_error_behavior:
>             ERROR_P     { $$ = makeJsonBehavior(JSON_BEHAVIOR_ERROR,
> NULL); }
>             | TRUE_P        { $$ = makeJsonBehavior(JSON_BEHAVIOR_TRUE,
> NULL); }
>             | FALSE_P       { $$ = makeJsonBehavior(JSON_BEHAVIOR_FALSE,
> NULL); }
>             | UNKNOWN       { $$ = makeJsonBehavior(JSON_BEHAVIOR_UNKNOWN,
> NULL); }
>         ;
>
> json_value_behavior:
>             NULL_P      { $$ = makeJsonBehavior(JSON_BEHAVIOR_NULL, NULL);
> }
>             | ERROR_P       { $$ = makeJsonBehavior(JSON_BEHAVIOR_ERROR,
> NULL); }
>             | DEFAULT a_expr    { $$ =
> makeJsonBehavior(JSON_BEHAVIOR_DEFAULT, $2); }
>         ;
>
> json_value_on_behavior_clause_opt:
>             json_value_behavior ON EMPTY_P
>                                     { $$.on_empty = $1; $$.on_error =
> NULL; }
>             | json_value_behavior ON EMPTY_P json_value_behavior ON ERROR_P
>                                     { $$.on_empty = $1; $$.on_error = $4; }
>             | json_value_behavior ON ERROR_P
>                                     { $$.on_empty = NULL; $$.on_error =
> $1; }
>             |  /* EMPTY */
>                                     { $$.on_empty = NULL; $$.on_error =
> NULL; }
>         ;
>
> json_query_behavior:
>             ERROR_P     { $$ = makeJsonBehavior(JSON_BEHAVIOR_ERROR,
> NULL); }
>             | NULL_P        { $$ = makeJsonBehavior(JSON_BEHAVIOR_NULL,
> NULL); }
>             | EMPTY_P ARRAY { $$ =
> makeJsonBehavior(JSON_BEHAVIOR_EMPTY_ARRAY, NULL); }
>             /* non-standard, for Oracle compatibility only */
>             | EMPTY_P       { $$ =
> makeJsonBehavior(JSON_BEHAVIOR_EMPTY_ARRAY, NULL); }
>             | EMPTY_P OBJECT_P  { $$ =
> makeJsonBehavior(JSON_BEHAVIOR_EMPTY_OBJECT, NULL); }
>             | DEFAULT a_expr    { $$ =
> makeJsonBehavior(JSON_BEHAVIOR_DEFAULT, $2); }
>         ;
>
> json_query_on_behavior_clause_opt:
>             json_query_behavior ON EMPTY_P
>                                     { $$.on_empty = $1; $$.on_error =
> NULL; }
>             | json_query_behavior ON EMPTY_P json_query_behavior ON ERROR_P
>                                     { $$.on_empty = $1; $$.on_error = $4; }
>             | json_query_behavior ON ERROR_P
>                                     { $$.on_empty = NULL; $$.on_error =
> $1; }
>             |  /* EMPTY */
>                                     { $$.on_empty = NULL; $$.on_error =
> NULL; }
>         ;
>
> Surely this can be made cleaner.
>
> By the way -- that comment about clauses being non-standard, can you
> spot exactly *which* clauses that comment applies to?
>
> --
> Álvaro Herrera         PostgreSQL Developer  —
> https://www.EnterpriseDB.com/
> "El número de instalaciones de UNIX se ha elevado a 10,
> y se espera que este número aumente" (UPM, 1972)
>
>
>

-- 
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/