Re: SQL/JSON: functions - Mailing list pgsql-hackers

On 1/5/22 00:51, Himanshu Upadhyaya wrote:
> On Thu, Dec 9, 2021 at 7:34 PM Himanshu Upadhyaya
> <upadhyaya.himanshu@gmail.com> wrote:
>> 3)
>> Is not that result of the two below queries should match because both are trying to retrieve the information from
theJSON object.
 
>>
>> postgres=# SELECT JSON_OBJECT('track' VALUE '{
>>     "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 101:39:21",
>>         "HR": 135
>>       }
>>     ]
>>   }
>> }')->'track'->'segments';
>>  ?column?
>> ----------
>>
>> (1 row)
>>
>> postgres=# 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",
>>         "HR": 135
>>       }
>>     ]
>>   }
>> }'::jsonb->'track'->'segments';
>>                                                                              ?column?
>>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>  [{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706,
13.2635],"start time": "2018-10-14 10:39:21"}]
 
>> (1 row)
>>
> just wanted to check your opinion on the above, is this an expected behaviour?


Your VALUE clause is actually not legal JSON - it has one too many
braces at the end. The reason postgres didn't complain about it is that
JSON_OBJECT is treating it as a string. If you correct the JSON and cast
it as jsonb you get the desired result:

andrew=# SELECT JSON_OBJECT('track' VALUE '{
    "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 101:39:21",
        "HR": 135
      }
    ]
  }'::jsonb)->'track'->'segments';
                                                                              ?column?
                                           
 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706,
13.2635],"start time": "2018-10-14 101:39:21"}]
 
(1 row)


>> Few comments For 0002-SQL-JSON-constructors-v59.patch:
> Also, any thoughts on this?



I will look at that separately.


cheers


andrew


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