Horton
Menu

Re: SQL/JSON: functions

Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>

View thread
From: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: 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:18:33Z
Lists: pgsql-hackers 
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.

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.