public inbox for [email protected]  

help / color / mirror / Atom feed

JSON query when object keys unnamed
2+ messages / 2 participants
[nested] [flat]

* JSON query when object keys unnamed
@ 2020-08-08 07:26  Chris Gormley <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Chris Gormley @ 2020-08-08 07:26 UTC (permalink / raw)
  To: [email protected]

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* RE: JSON query when object keys unnamed
@ 2020-08-08 10:04  Aleksey M Boltenkov <[email protected]>
  parent: Chris Gormley <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Aleksey M Boltenkov @ 2020-08-08 10:04 UTC (permalink / raw)
  To: [email protected]; +Cc: [email protected]

  08.08.2020, 10:27, Chris Gormley <[email protected]>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.


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2020-08-08 10:04 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-08 07:26 JSON query when object keys unnamed Chris Gormley <[email protected]>
2020-08-08 10:04 ` Aleksey M Boltenkov <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox