JSON query when object keys unnamed

From: Chris Gormley <chris(dot)gormley2(at)gmail(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: JSON query when object keys unnamed
Date: 2020-08-08 07:26:45
Message-ID: D4E712D7-EFF7-437C-AEB8-ED3AB09E2D5F@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.
Thanks
Chris

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Aleksey M Boltenkov 2020-08-08 10:04:26 RE: JSON query when object keys unnamed
Previous Message Lætitia Avrot 2020-08-05 11:45:34 Re: How to post a question