Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XLKtW-0003pd-Tc for pgsql-sql@arkaria.postgresql.org; Sat, 23 Aug 2014 23:38:51 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XLKtW-0000rT-8e for pgsql-sql@arkaria.postgresql.org; Sat, 23 Aug 2014 23:38:50 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XLKtV-0000rM-5V for pgsql-sql@postgresql.org; Sat, 23 Aug 2014 23:38:49 +0000 Received: from bay004-omc3s2.hotmail.com ([65.54.190.140]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XLKtN-0007TE-Jj for pgsql-sql@postgresql.org; Sat, 23 Aug 2014 23:38:47 +0000 Received: from BAY178-W41 ([65.54.190.188]) by BAY004-OMC3S2.hotmail.com with Microsoft SMTPSVC(7.5.7601.22712); Sat, 23 Aug 2014 16:38:39 -0700 X-TMN: [jPxm1tzcly6YFUX1O+2g3/Bmc1/R1yDoYksFiAiMKWA=] X-Originating-Email: [hm34306@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_aeb9cac8-6149-4f38-adfa-54e3042d4308_" From: Hector Menchaca To: David G Johnston , "pgsql-sql@postgresql.org" Subject: Re: postgres json: How to query map keys to get children Date: Sat, 23 Aug 2014 18:38:39 -0500 Importance: Normal In-Reply-To: <1408825132229-5816009.post@n5.nabble.com> References: , <1408825132229-5816009.post@n5.nabble.com> MIME-Version: 1.0 X-OriginalArrivalTime: 23 Aug 2014 23:38:39.0714 (UTC) FILETIME=[5DE9DC20:01CFBF2B] X-Pg-Spam-Score: -0.9 (/) 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 --_aeb9cac8-6149-4f38-adfa-54e3042d4308_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Perfect your snippet gave me some clues... It looks as follows: SELECT json_array_elements(skill_type.Skill->'value')->>'Name' as NameFROM= ( SELECT to_json(json_each(ResourceDocument->'Skill')) as Skill FROM testd= epot.Resource) skill_type to_json returns a key value map which you then use to get to the json array Thanks for the lead :) > Date: Sat=2C 23 Aug 2014 13:18:52 -0700 > From: david.g.johnston@gmail.com > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] postgres json: How to query map keys to get children >=20 > Hector Menchaca wrote > > json_array_elements(ResourceDocument->'Skill'->*) >=20 > NOT TESTED (or complete) >=20 > SELECT skill_type.value->'Name' > FROM ( > SELECT * FROM json_each(rd->'Skill') > ) skill_type >=20 > Because you want columns for Name=2C etc=2C you must list those explicitl= y > instead of using json_each over those. >=20 > David J. >=20 >=20 >=20 > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/pos= tgres-json-How-to-query-map-keys-to-get-children-tp5816001p5816009.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. >=20 >=20 > --=20 > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql = --_aeb9cac8-6149-4f38-adfa-54e3042d4308_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Perfect your snippet gave me som= e clues...

It looks as follows:

SELECT  =3Bjson_array_elements(skill_type.Skill->=3B'value')-&g= t=3B>=3B'Name' as Name
FROM (
SELECT to_json(json_each(Resource= Document->=3B'Skill')) as Skill
FROM testdepot.Resource
) skill= _type

to_json returns a key value map which you th= en use to get to the json array

Thanks for the lea= d :)

>=3B Date: Sat=2C 23 Aug 2014 13:18:52 -0700
>=3B= From: david.g.johnston@gmail.com
>=3B To: pgsql-sql@postgresql.org>=3B Subject: Re: [SQL] postgres json: How to query map keys to get chil= dren
>=3B
>=3B Hector Menchaca wrote
>=3B >=3B json_arra= y_elements(ResourceDocument->=3B'Skill'->=3B*)
>=3B
>=3B NOT= TESTED (or complete)
>=3B
>=3B SELECT skill_type.value->=3B'N= ame'
>=3B FROM (
>=3B SELECT * FROM json_each(rd->=3B'Skill')>=3B ) skill_type
>=3B
>=3B Because you want columns for Nam= e=2C etc=2C you must list those explicitly
>=3B instead of using json_= each over those.
>=3B
>=3B David J.
>=3B
>=3B
>= =3B
>=3B --
>=3B View this message in context: http://postgresql= .1045698.n5.nabble.com/postgres-json-How-to-query-map-keys-to-get-children-= tp5816001p5816009.html
>=3B Sent from the PostgreSQL - sql mailing lis= t archive at Nabble.com.
>=3B
>=3B
>=3B --
>=3B Sent= via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>=3B To make ch= anges to your subscription:
>=3B http://www.postgresql.org/mailpref/pg= sql-sql
= --_aeb9cac8-6149-4f38-adfa-54e3042d4308_--