public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrew Dunstan <[email protected]>
To: Himanshu Upadhyaya <[email protected]>
Cc: Nikita Glukhov <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Dmitry Dolgov <[email protected]>
Cc: Oleg Bartunov <[email protected]>
Cc: Erik Rijkers <[email protected]>
Subject: Re: SQL/JSON: functions
Date: Wed, 5 Jan 2022 12:03:15 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPF61jD7hJQZXi1oQs7SyZeJ1uJ1JDE8aUmTFovLpGfOiOdKLA@mail.gmail.com>
References: <[email protected]>
<CALNJ-vSmJbaoUm7nuyV65UdeYTee2UxqG49CZ7CjR_rEB5ZdJg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CALtqXTdHvT+tsGm4=aGL9=vFuiRX=dk0fHGtAjqCe=QSQmWKwA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAD5tBcL5pr673RUED4Diy4PV7yKpY1HX_rt0N_W1d9XtLoC7kA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAPF61jDv+qf4ZTm16quEQ5_DKXb0nSZ1OUTN7g2ELDssYLWrVQ@mail.gmail.com>
<CAPF61jD7hJQZXi1oQs7SyZeJ1uJ1JDE8aUmTFovLpGfOiOdKLA@mail.gmail.com>
On 1/5/22 00:51, Himanshu Upadhyaya wrote:
> On Thu, Dec 9, 2021 at 7:34 PM Himanshu Upadhyaya
> <[email protected]> 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
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL/JSON: functions
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox