Re: SQL/JSON: functions

út 4. 1. 2022 v 10:19 odesílatel Himanshu Upadhyaya <
upadhyaya(dot)himanshu(at)gmail(dot)com> napsal:

> On Thu, Dec 16, 2021 at 3:06 AM Andrew Dunstan <andrew(at)dunslane(dot)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
>
>
>