Thread: JSON query when object keys unnamed
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
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.
Thanks
Chris
08.08.2020, 10:27, Chris Gormley <chris.gormley2@gmail.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:
SELECTdata->’MEColl’->0This 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.