SELECT json_array_elements(skill_type.Skill->'value')->>'Name' as Name
FROM (
SELECT to_json(json_each(ResourceDocument->'Skill')) as Skill
FROM testdepot.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, 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 > > Hector Menchaca wrote > > json_array_elements(ResourceDocument->'Skill'->*) > > NOT TESTED (or complete) > > SELECT skill_type.value->'Name' > FROM ( > SELECT * FROM json_each(rd->'Skill') > ) skill_type > > Because you want columns for Name, etc, you must list those explicitly > instead of using json_each over those. > > David J. > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-json-How-to-query-map-keys-to-get-children-tp5816001p5816009.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql