public inbox for [email protected]  

help / color / mirror / Atom feed

From: Chris Gormley <[email protected]>
To: [email protected]
Subject: JSON query when object keys unnamed
Date: Sat, 8 Aug 2020 08:26:45 +0100
Message-ID: <[email protected]> (raw)

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

view thread (2+ messages)  latest in thread

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]
  Subject: Re: JSON query when object keys unnamed
  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