Re: SQL/JSON: functions

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.

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