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 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(dot)g(dot)johnston(at)gmail(dot)com
> To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql