Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k4LiJ-0003yz-TC for pgsql-novice@arkaria.postgresql.org; Sat, 08 Aug 2020 10:04:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k4LiI-0006zP-Jt for pgsql-novice@arkaria.postgresql.org; Sat, 08 Aug 2020 10:04:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k4LiI-0006zI-6l for pgsql-novice@lists.postgresql.org; Sat, 08 Aug 2020 10:04:30 +0000 Received: from mxout2.rambler.ru ([81.19.78.101]) by magus.postgresql.org with esmtps (TLS1.1:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1k4LiF-0000lx-PG for pgsql-novice@lists.postgresql.org; Sat, 08 Aug 2020 10:04:29 +0000 Received: from saddam2.rambler.ru (saddam2.rambler.ru [10.32.16.2]) by mxout2.rambler.ru (Postfix) with ESMTP id 9D2D78DC303; Sat, 8 Aug 2020 13:04:26 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=rambler.ru; s=mail; t=1596881066; bh=Rblz7VNvbd+JeNdcBXpdTKvntUrBMb7iBoZ7POnzybQ=; h=From:To:Cc:Reply-To:Subject:Date:In-Reply-To:References; b=ZyqwDzesSws5xlfolqqdTETtFzrOcFoXY0/XU7TI5WCAuG3bs+JGzLRUHoo7+9Y+h u/ij38ketruMmROi+h4U2wNg+PEJNdueBkL4HDtYs2DlVnDTEufR+4/zjkAaG74cOW H1QfV4C/S33IKXR/wfPM8ljDECqrix3Jx76DcK6M= Received: from localhost.localdomain (localhost [127.0.0.1]) by saddam2.rambler.ru (Postfix) with ESMTP id 7CE98C7C279; Sat, 8 Aug 2020 13:04:26 +0300 (MSK) Received: from [93.123.182.191] by mail.rambler.ru with HTTP; Sat, 8 Aug 2020 13:04:26 +0300 From: "Aleksey M Boltenkov" To: chris.gormley2@gmail.com Cc: pgsql-novice@lists.postgresql.org Reply-To: "Aleksey M Boltenkov" Subject: RE: JSON query when object keys unnamed Date: Sat, 8 Aug 2020 13:04:26 +0300 Content-Transfer-Encoding: 7bit Content-Type: multipart/alternative; boundary="_----------=_1596881066302314" In-Reply-To: Message-Id: <1596881066.444554.3023.42890@mail.rambler.ru> MIME-Version: 1.0 References: X-Mailer: Rambler WebMail, http://mail.rambler.ru/ X-Rambler-User: holybolt@rambler.ru/93.123.182.191 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --_----------=_1596881066302314 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8"; format="flowed" 08.08.2020, 10:27, Chris Gormley Hi all, I=E2=80=99m new to this, so I hope someone can help me. I have the follow= ing JSON in a data field that I=E2=80=99m trying to separate into its component parts= but having difficulty with the unnamed object keys: { =E2=80=9CCId=E2=80=9D : =E2=80=9Dxxx=E2=80=9D, =E2=80=9DEId=E2=80=9D : =E2=80=9Dxxx=E2=80=9D, =E2=80=9CMEColl=E2=80=9D : [{ =E2=80=9CKey=E2=80=9D : =E2=80=9CTrans.PLF=E2=80=9D , =E2=80=9CValue=E2=80=9D : =E2=80=9C0001=E2=80=9D }, { =E2=80=9CKey=E2=80=9D : =E2=80=9CTrans.BA=E2=80=9D, =E2=80=9CValue=E2=80=9D : =E2=80=9C8.0=E2=80=9D }, { =E2=80=9CKey=E2=80=9D : =E2=80=9CTrans.TS=E2=80=9D, =E2=80=9CValue=E2=80=9D : =E2=80=9C2020-05-01T00:00:00=E2=80=9D }] =E2=80=9CMId=E2=80=9D : =E2=80=9Cxxx=E2=80=9D } So the question is, how do I extract each of the 3 x values from the key/value pairs for =E2=80=9CMEColl=E2=80=9D as they all have the name of= =E2=80=9CKey=E2=80=9D rather than a unique name? So far, I=E2=80=99ve managed to isolate the first key/value pair using: SELECT data->=E2=80=99MEColl=E2=80=99->0 This returns: {=E2=80=9CKey=E2=80=9D : =E2=80=9CTrans.PLF=E2=80=9D , =E2=80=9CValue=E2= =80=9D : =E2=80=9C0001=E2=80=9D} But what I need are the values =E2=80=9CTrans.PLF=E2=80=9D and =E2=80=9C0= 001=E2=80=9D 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=E2=80=99t seem t= o 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 =E2=94=82 eid =E2=94=82 mid =E2=94=82 key =E2=94=82 value =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90 "xxx" =E2=94=82 "xxx" =E2=94=82 "xxx" =E2=94=82 "Trans.PLF" =E2=94=82 "0001" "xxx" =E2=94=82 "xxx" =E2=94=82 "xxx" =E2=94=82 "Trans.BA" =E2=94=82 "8.0" "xxx" =E2=94=82 "xxx" =E2=94=82 "xxx" =E2=94=82 "Trans.TS" =E2=94=82 "2020-= 05-01T00:00:00" (3 rows) Aleksey M Boltenkov. --_----------=_1596881066302314 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"
08.08.2020, 10:27, Chris Gormley <ch= ris.gormley2@gmail.com>
Hi all= ,
I=E2=80=99m new to this, so I hope someone can help me. I= have the following JSON in a data field that I=E2=80=99m trying to separat= e into its component parts but having difficulty with the unnamed object ke= ys:

{
   =E2=80=9CCId= =E2=80=9D : =E2=80=9Dxxx=E2=80=9D,
   =E2= =80=9DEId=E2=80=9D : =E2=80=9Dxxx=E2=80=9D,
  &n= bsp;=E2=80=9CMEColl=E2=80=9D : [{
    =      =E2=80=9CKey=E2=80=9D : =E2=80=9CTrans.PLF=E2= =80=9D ,
        &= nbsp;=E2=80=9CValue=E2=80=9D : =E2=80=9C0001=E2=80=9D
&nbs= p;  }, {
      &nb= sp;  =E2=80=9CKey=E2=80=9D : =E2=80=9CTrans.BA=E2=80=9D,          =E2=80=9CVal= ue=E2=80=9D : =E2=80=9C8.0=E2=80=9D
   }, {=
         =E2= =80=9CKey=E2=80=9D : =E2=80=9CTrans.TS=E2=80=9D,
 &nb= sp;       =E2=80=9CValue=E2=80=9D : =E2= =80=9C2020-05-01T00:00:00=E2=80=9D
   }]
   =E2=80=9CMId=E2=80=9D : =E2=80=9Cxxx=E2=80= =9D
}


So the question is, how do I = extract each of the 3 x values from the key/value pairs for =E2=80=9CMEColl= =E2=80=9D as they all have the name of =E2=80=9CKey=E2=80=9D rather than a = unique name?


So far, I=E2=80=99ve managed to isolat= e the first key/value pair using:

SELECT
     data->=E2=80=99MEColl=E2=80=99->0


This returns= :

{=E2=80=9CKey=E2=80= =9D : =E2=80=9CTrans.PLF=E2=80=9D , =E2=80=9CValue=E2=80=9D : =E2=80=9C0001=E2=80=9D}


But what I need are the = values =E2=80=9CTrans.PLF=E2=80=9D and =E2=80=9C0001=E2=80=9D as well as th= e other values of the other 2 x key/value pairs.

I think I might have to use jsonb_array_elements but can=E2=80=99t s= eem to get the syntax right.
=
I know this is s= imple stuff for the experienced, but struggling despite trying read most of= the internet to resolve myself.

Any help gre= atly appreciated.
Thanks<= /span>
Chris

with x as (select '{=

   "CId" : "xxx",
   "= EId" : "xxx",
   "MEColl" : [{
    &n= bsp;    "Key" : "Trans.PLF" ,
    &n= bsp;    "Value" : "0001"
   }, {
 &nb= sp;       "Key" : "Trans.BA",
  = ;       "Value" : "8.0"
   }, {=
         "Key" : "Trans.TS",          "Value" : "2020-05-01T0= 0:00:00"
   }],
   "MId" : "xxx"
}'::jsonb = val)
select val->'CId' CId, val->'EId' EId, val->'MId' MId, js= onb_array_elements(val->'MEColl')->'Key' as key, jsonb_array_elements= (val->'MEColl')->'Value' as value from x;


cid =E2=94=82 eid =E2= =94=82 mid =E2=94=82 key =E2=94=82 value
=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
"= xxx" =E2=94=82 "xxx" =E2=94=82 "xxx" =E2=94=82 "Trans.PLF" =E2=94=82 "0001"=
"xxx" =E2=94=82 "xxx" =E2=94=82 "xxx" =E2=94=82 "Trans.BA" =E2=94=82 "= 8.0"
"xxx" =E2=94=82 "xxx" =E2=94=82 "xxx" =E2=94=82 "Trans.TS" =E2=94= =82 "2020-05-01T00:00:00"
(3 rows)



Aleksey M Boltenkov.

= --_----------=_1596881066302314--