Horton
Menu

Re: SQL/JSON: functions

Pavel Stehule <pavel.stehule@gmail.com>

View thread
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>
Cc: Andrew Dunstan <andrew@dunslane.net>, Nikita Glukhov <n.gluhov@postgrespro.ru>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Dmitry Dolgov <9erthalion6@gmail.com>, Oleg Bartunov <obartunov@postgrespro.ru>, Erik Rijkers <er@xs4all.nl>
Date: 2022-01-04T09:55:06Z
Lists: pgsql-hackers 
út 4. 1. 2022 v 10:19 odesílatel Himanshu Upadhyaya <
upadhyaya.himanshu@gmail.com> napsal:

> On Thu, Dec 16, 2021 at 3:06 AM Andrew Dunstan <andrew@dunslane.net>
> wrote:
> >
> >
> > On 12/9/21 09:04, Himanshu Upadhyaya wrote:
> > >
> > >
> > >
> > > 4)
> > > Are we intentionally allowing numeric keys in JSON_OBJECT but somehow
> > > these are not allowed in ORACLE?
> > > ‘postgres[151876]=#’select JSON_OBJECT( 3+1:2, 2+2:1);
> > >     json_object
> > > --------------------
> > >  {"4" : 2, "4" : 1}
> > > (1 row)
> > >
> > > In ORACLE we are getting error("ORA-00932: inconsistent datatypes:
> > > expected CHAR got NUMBER") which seems to be more reasonable.
> > > "ORA-00932: inconsistent datatypes: expected CHAR got NUMBER"
> > >
> > > Postgres is also dis-allowing below then why allow numeric keys in
> > > JSON_OBJECT?
> > > ‘postgres[151876]=#’select '{
> > >   "track": {
> > >     "segments": [
> > >       {
> > >         "location":   [ 47.763, 13.4034 ],
> > >         "start time": "2018-10-14 10:05:14",
> > >         "HR": 73
> > >       },
> > >       {
> > >         "location":   [ 47.706, 13.2635 ],
> > >         "start time": "2018-10-14 10:39:21",
> > >         3: 135
> > >       }
> > >     ]
> > >   }
> > > }'::jsonb;
> > > ERROR:  22P02: invalid input syntax for type json
> > > LINE 1: select '{
> > >                ^
> > > DETAIL:  Expected string, but found "3".
> > > CONTEXT:  JSON data, line 12:         3...
> > > LOCATION:  json_ereport_error, jsonfuncs.c:621
> > >
> > > Also, JSON_OBJECTAGG is failing if we have any numeric key, however,
> > > the message is not very appropriate.
> > > SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL) AS apt
> > > FROM (VALUES ('no', 5), ('area', 50), ('rooms', 2), ('foo', NULL),
> > > (5,5)) kv(k, v);
> > > ERROR:  22P02: invalid input syntax for type integer: "no"
> > > LINE 2: FROM (VALUES ('no', 5), ('area', 50), ('rooms', 2), ('foo', ...
> > >                       ^
> > > LOCATION:  pg_strtoint32, numutils.c:320
> > >
> > >
> > >
> >
> > The literal above is simply not legal json, so the json parser is going
> > to reject it outright. However it is quite reasonable for JSON
> > constructors to convert non-string key values to strings. Otherwise we'd
> > be rejecting not just numbers but for example dates as key values. c.f.
> > json_build_object(), the documentation for which says "Key arguments are
> > coerced to text."
> >
> Yes Agree on this, but just thinking if we can differentiate dates and
> numeric keys to have consistent behaviour and simply reject if we have
> numeric keys(to match it with the behaviour of JSON parser) because
> JSON with numeric keys is actually not a valid JSON.
>
> +1

Pavel



> SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL) AS apt
> FROM (VALUES ('no', 5), ('area', 50), ('rooms', 2), ('foo', NULL),
> (5,5)) kv(k, v);
> ERROR:  22P02: invalid input syntax for type integer: "no"
> LINE 2: FROM (VALUES ('no', 5), ('area', 50), ('rooms', 2), ('foo', ...
>                       ^
> LOCATION:  pg_strtoint32, numutils.c:320
>
> Above call to JSON_OBJECTAGG is failing because we have the numeric
> key, is not that it also needs to follow the same context  of
> converting key argument to text? or both(JSON_OBJECTAGG  and
> JSON_OBJECT) should not allow numeric keys in the JSON object and
> allow date (if that is the only use case)?
>
> Thoughts?
> --
> Regards,
> Himanshu Upadhyaya
> EnterpriseDB: http://www.enterprisedb.com
>
>
>

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.