Re: SQL/JSON: functions

On 1/5/22 00:51, Himanshu Upadhyaya wrote:
> On Thu, Dec 9, 2021 at 7:34 PM Himanshu Upadhyaya
> <upadhyaya(dot)himanshu(at)gmail(dot)com> wrote:
>> 3)
>> Is not that result of the two below queries should match because both are trying to retrieve the information from the JSON 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