Re: SQL/JSON: functions

On 1/4/22 04:18, Himanshu Upadhyaya wrote:
> On Thu, Dec 16, 2021 at 3:06 AM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>
>>
>> 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)?
>>

this error has nothing at all to do with the json code. You simply have
an invalid VALUES expression:

postgres=# select * FROM (VALUES ('no', 5), ('area', 50), ('rooms', 2),
('foo', NULL), (5,5)) kv(k, v);
ERROR:  invalid input syntax for type integer: "no"
LINE 1: select * FROM (VALUES ('no', 5), ('area', 50), ('rooms', 2),...

cheers

andrew

--

Andrew Dunstan
EDB: https://www.enterprisedb.com