út 4. 1. 2022 v 10:19 odesílatel Himanshu Upadhyaya < upadhyaya.himan...@gmail.com> napsal:
> On Thu, Dec 16, 2021 at 3:06 AM Andrew Dunstan <and...@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 > > >