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."
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com