Re: SQL/JSON: functions

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