Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XLFeI-0004XV-4v for pgsql-sql@arkaria.postgresql.org; Sat, 23 Aug 2014 18:02:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XLFeH-00029I-KG for pgsql-sql@arkaria.postgresql.org; Sat, 23 Aug 2014 18:02:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XLFeG-00029C-Tn for pgsql-sql@postgresql.org; Sat, 23 Aug 2014 18:02:44 +0000 Received: from bay004-omc2s11.hotmail.com ([65.54.190.86]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XLFeC-0007j5-Jz for pgsql-sql@postgresql.org; Sat, 23 Aug 2014 18:02:43 +0000 Received: from BAY178-W51 ([65.54.190.124]) by BAY004-OMC2S11.hotmail.com with Microsoft SMTPSVC(7.5.7601.22712); Sat, 23 Aug 2014 11:02:37 -0700 X-TMN: [DoG+UwqxLXfucFTIYJ2BnXhwUZwZQ8NuCf3fRLT/Wj4=] X-Originating-Email: [hm34306@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_677c7eef-0092-443c-9870-28b00fd16b16_" From: Hector Menchaca To: "pgsql-sql@postgresql.org" Subject: postgres json: How to query map keys to get children Date: Sat, 23 Aug 2014 13:02:37 -0500 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 23 Aug 2014 18:02:37.0920 (UTC) FILETIME=[6C8C3E00:01CFBEFC] X-Pg-Spam-Score: -2.3 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_677c7eef-0092-443c-9870-28b00fd16b16_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm new to postgresql and I am having trouble finding an example of how to = query the following: { "Skill": { "Technical": [ { "Name": "C#"=2C "Ra= ting": 4=2C "Last Used": "2014-08-21" }=2C { "Name": "r= uby"=2C "Rating": 4=2C "Last Used": "2014-08-21" } = ]=2C "Product": [ { "Name": "MDM"=2C "Rating": 4= =2C "Last Used": "2014-08-21" }=2C { "Name": "UDM"=2C = "Rating": 5=2C "Last Used": "2014-08-21" } ] = } } In short I struggling with understanding how to query through maps without = having to be explicit about naming each key. I have a query that does the following=2C though it seems a bit much to hav= e to do... Select 'Technical' as SkillType =2C json_array_elements(ResourceD= ocument->'Skill'->'Technical')->>'Name' as SkillName =2C json_array_e= lements(ResourceDocument->'Skill'->'Technical')->>'Rating' as Rating = =2C json_array_elements(ResourceDocument->'Skill'->'Technical')->>'Last Use= d' as LastUsed FROM testdepot.Resource UNION ALL Sele= ct 'Product' as SkillType =2C json_array_elements(ResourceDocument->'S= kill'->'Product')->>'Name' as SkillName =2C json_array_elements(Resou= rceDocument->'Skill'->'Product')->>'Rating' as Rating =2C json_array_e= lements(ResourceDocument->'Skill'->'Product')->>'Last Used' as LastUsed = FROM testdepot.Resource I am trying to find a way to do this in 1 query that allows containing all = keys of a map.In this case Product and TechnicalSomething like: Select 'Product' as SkillType =2C json_array_elements(ResourceDocu= ment->'Skill'->*)->>'Name' as SkillName =2C json_array_elements(Resou= rceDocument->'Skill'->*)->>'Rating' as Rating =2C json_array_elements(= ResourceDocument->'Skill'->*)->>'Last Used' as LastUsed FROM testdepot.= Resource = --_677c7eef-0092-443c-9870-28b00fd16b16_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I'm new to postgresql and I= am having trouble finding an example of how to query the following:
<= div>
 =3B  =3B {
 =3B  =3B "Skill":= {
 =3B  =3B "Technical": [
 =3B  =3B { "Name": "C#"=2C<= /div>
 =3B  =3B  =3B"Rating": 4=2C
 =3B  =3B  =3B"L= ast Used": "2014-08-21"
 =3B  =3B }=2C
 =3B  = =3B { "N= ame": "ruby"=2C
 =3B  =3B  =3B"Rating": 4=2C
 = =3B  =3B  =3B"Last Used": "2014-08-21"
 =3B  =3B }
&nb= sp=3B  =3B =3B
 =3B  =3B ]=2C
 =3B  =3B = "Product"= : [
 =3B  =3B { "Name": "MDM"=2C
 =3B  =3B  =3B"Ra= ting": 4=2C
 =3B  =3B  =3B"Last Used": "2014-08-21"
 =3B  =3B }=2C
 =3B  =3B { "Name": "UDM"=2C
 =3B =  =3B  =3B"Rating": 5=2C
 =3B  =3B  =3B"Last Used": "2014-08= -21"
 =3B  =3B }
 =3B  =3B ]
 =3B  =3B= }
 =3B  =3B }

In short I struggling with = understanding how to query through maps without having to be explicit about= naming each key.

I have a query that does the fol= lowing=2C though it seems a bit much to have to do...

<= div> =3B  =3B Select  =3B'Technical' as SkillType
&nb= sp=3B  =3B <= /span>=2C json_array_elements(ResourceDocument->=3B'Skill'->=3B'Technic= al')->=3B>=3B'Name' as SkillName
 =3B  =3B =2C json_array_elements(Resou= rceDocument->=3B'Skill'->=3B'Technical')->=3B>=3B'Rating' as Rating=
 =3B  =3B =2C json_array_elements(ResourceDocument->=3B'Skill'-= >=3B'Technical')->=3B>=3B'Last Used' as LastUsed
 =3B &= nbsp=3B FR= OM testdepot.Resource
 =3B  =3B =3B
 = =3B  =3B UNION ALL
 =3B  =3B
 =3B  =3B Select 'Product' as Skil= lType
 =3B  =3B =2C json_array_elements(ResourceDocument->=3B'Sk= ill'->=3B'Product')->=3B>=3B'Name' as SkillName
 =3B  =3B =2C json_arr= ay_elements(ResourceDocument->=3B'Skill'->=3B'Product')->=3B>=3B'Ra= ting' as Rating
 =3B  =3B =2C json_array_elements(ResourceDocument= ->=3B'Skill'->=3B'Product')->=3B>=3B'Last Used' as LastUsed
 =3B  =3B FROM testdepot.Resource

I am trying to = find a way to do this in 1 query that allows containing all keys of a map.<= /div>
In this case Product and Technical
Something like:

 =3B  =3B Select 'Product' as SkillType
<= div> =3B  =3B =2C json_array_elements(ResourceDocument->=3B'Skill'->=3B*= )->=3B>=3B'Name' as SkillName
 =3B  =3B =2C json_array_elements(Resource= Document->=3B'Skill'->=3B*)->=3B>=3B'Rating' as Rating
&n= bsp=3B  =3B = =2C json_array_elements(ResourceDocument->=3B'Skill'->=3B*)->= =3B>=3B'Last Used' as LastUsed
 =3B  =3B FROM testdepot.Resource<= /div>




=
= --_677c7eef-0092-443c-9870-28b00fd16b16_--