Message-ID: <530d18d8-e4d8-f0e6-c7ff-e499672710c0@dunslane.net>
Date: Wed, 5 Jan 2022 12:03:15 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.2.0
Subject: Re: SQL/JSON: functions
Content-Language: en-US
To: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>
Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>,
 Dmitry Dolgov <9erthalion6@gmail.com>,
 Oleg Bartunov <obartunov@postgrespro.ru>, Erik Rijkers <er@xs4all.nl>
References: <be40362b-7821-7422-d33f-fbf1c61bb3e3@postgrespro.ru>
 <CALNJ-vSmJbaoUm7nuyV65UdeYTee2UxqG49CZ7CjR_rEB5ZdJg@mail.gmail.com>
 <5f8987e9-4c3d-6402-b18c-b3e84b786739@postgrespro.ru>
 <0feb891c5a60d17eca49dc77c93d86fb@xs4all.nl>
 <CALtqXTdHvT+tsGm4=aGL9=vFuiRX=dk0fHGtAjqCe=QSQmWKwA@mail.gmail.com>
 <ba5eb2a3-fe35-66bc-2ed1-a3624f15ec8a@dunslane.net>
 <edd10ba6-8600-075f-f035-e11f8de7ab30@dunslane.net>
 <ea71320c-0fdb-522d-4ada-f2bd42d173f9@dunslane.net>
 <24c35f9a-d11d-855e-ebfc-5638993c8d99@postgrespro.ru>
 <CAD5tBcL5pr673RUED4Diy4PV7yKpY1HX_rt0N_W1d9XtLoC7kA@mail.gmail.com>
 <55a4a276-2ed7-077b-8c4e-8209b58ebbf5@dunslane.net>
 <cbda15f2-3f3c-434a-51b3-772b60901f0b@2ndQuadrant.com>
 <264268a3-f836-487f-b56e-0a19739ca03b@dunslane.net>
 <97da94b2-dcfd-20cf-2ed7-b9f68c6b6c16@2ndQuadrant.com>
 <50df2280-7c7a-dffd-a942-9c67b1550e2c@dunslane.net>
 <CAPF61jDv+qf4ZTm16quEQ5_DKXb0nSZ1OUTN7g2ELDssYLWrVQ@mail.gmail.com>
 <CAPF61jD7hJQZXi1oQs7SyZeJ1uJ1JDE8aUmTFovLpGfOiOdKLA@mail.gmail.com>
From: Andrew Dunstan <andrew@dunslane.net>
In-Reply-To: <CAPF61jD7hJQZXi1oQs7SyZeJ1uJ1JDE8aUmTFovLpGfOiOdKLA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/530d18d8-e4d8-f0e6-c7ff-e499672710c0%40dunslane.net>
Precedence: bulk


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 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