From: | "Aleksey M Boltenkov" <holybolt(at)rambler(dot)ru> |
---|---|
To: | chris(dot)gormley2(at)gmail(dot)com |
Cc: | pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | RE: JSON query when object keys unnamed |
Date: | 2020-08-08 10:04:26 |
Message-ID: | 1596881066.444554.3023.42890@mail.rambler.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
08.08.2020, 10:27, Chris Gormley <chris(dot)gormley2(at)gmail(dot)com>Hi all,
I’m new to this, so I hope someone can help me. I have the following JSON in
a data field that I’m trying to separate into its component parts but having
difficulty with the unnamed object keys:
{
“CId” : ”xxx”,
”EId” : ”xxx”,
“MEColl” : [{
“Key” : “Trans.PLF” ,
“Value” : “0001”
}, {
“Key” : “Trans.BA”,
“Value” : “8.0”
}, {
“Key” : “Trans.TS”,
“Value” : “2020-05-01T00:00:00”
}]
“MId” : “xxx”
}
So the question is, how do I extract each of the 3 x values from the
key/value pairs for “MEColl” as they all have the name of “Key” rather than a
unique name?
So far, I’ve managed to isolate the first key/value pair using:
SELECT data->’MEColl’->0
This returns:
{“Key” : “Trans.PLF” , “Value” : “0001”}
But what I need are the values “Trans.PLF” and “0001” as well as the other
values of the other 2 x key/value pairs.
I think I might have to use jsonb_array_elements but can’t seem to get the
syntax right.
I know this is simple stuff for the experienced, but struggling despite
trying read most of the internet to resolve myself.
Any help greatly appreciated.ThanksChris
with x as (select '{
"CId" : "xxx",
"EId" : "xxx",
"MEColl" : [{
"Key" : "Trans.PLF" ,
"Value" : "0001"
}, {
"Key" : "Trans.BA",
"Value" : "8.0"
}, {
"Key" : "Trans.TS",
"Value" : "2020-05-01T00:00:00"
}],
"MId" : "xxx"
}'::jsonb val)
select val->'CId' CId, val->'EId' EId, val->'MId' MId,
jsonb_array_elements(val->'MEColl')->'Key' as key,
jsonb_array_elements(val->'MEColl')->'Value' as value from x;
cid │ eid │ mid │ key │ value
═══════╪═══════╪═══════╪═════════════╪═══════════════════════
"xxx" │ "xxx" │ "xxx" │ "Trans.PLF" │ "0001"
"xxx" │ "xxx" │ "xxx" │ "Trans.BA" │ "8.0"
"xxx" │ "xxx" │ "xxx" │ "Trans.TS" │ "2020-05-01T00:00:00"
(3 rows)
Aleksey M Boltenkov.
From | Date | Subject | |
---|---|---|---|
Next Message | Akib Rhast | 2020-08-11 18:13:10 | Trouble running pgadmin container on heroku |
Previous Message | Chris Gormley | 2020-08-08 07:26:45 | JSON query when object keys unnamed |