agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedpostgres json: How to query map keys to get children
3+ messages / 2 participants
[nested] [flat]
* postgres json: How to query map keys to get children
@ 2014-08-23 18:02 Hector Menchaca <hm34306@hotmail.com>
2014-08-23 20:18 ` Re: postgres json: How to query map keys to get children David G Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Hector Menchaca @ 2014-08-23 18:02 UTC (permalink / raw)
To: pgsql-sql
I'm new to postgresql and I am having trouble finding an example of how to query the following:
{ "Skill": { "Technical": [ { "Name": "C#", "Rating": 4, "Last Used": "2014-08-21" }, { "Name": "ruby", "Rating": 4, "Last Used": "2014-08-21" } ], "Product": [ { "Name": "MDM", "Rating": 4, "Last Used": "2014-08-21" }, { "Name": "UDM", "Rating": 5, "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, though it seems a bit much to have to do...
Select 'Technical' as SkillType , json_array_elements(ResourceDocument->'Skill'->'Technical')->>'Name' as SkillName , json_array_elements(ResourceDocument->'Skill'->'Technical')->>'Rating' as Rating , json_array_elements(ResourceDocument->'Skill'->'Technical')->>'Last Used' as LastUsed FROM testdepot.Resource UNION ALL Select 'Product' as SkillType , json_array_elements(ResourceDocument->'Skill'->'Product')->>'Name' as SkillName , json_array_elements(ResourceDocument->'Skill'->'Product')->>'Rating' as Rating , json_array_elements(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 , json_array_elements(ResourceDocument->'Skill'->*)->>'Name' as SkillName , json_array_elements(ResourceDocument->'Skill'->*)->>'Rating' as Rating , json_array_elements(ResourceDocument->'Skill'->*)->>'Last Used' as LastUsed FROM testdepot.Resource
=
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: postgres json: How to query map keys to get children
2014-08-23 18:02 postgres json: How to query map keys to get children Hector Menchaca <hm34306@hotmail.com>
@ 2014-08-23 20:18 ` David G Johnston <david.g.johnston@gmail.com>
2014-08-23 23:38 ` Re: postgres json: How to query map keys to get children Hector Menchaca <hm34306@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David G Johnston @ 2014-08-23 20:18 UTC (permalink / raw)
To: pgsql-sql
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-tp581600...
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: postgres json: How to query map keys to get children
2014-08-23 18:02 postgres json: How to query map keys to get children Hector Menchaca <hm34306@hotmail.com>
2014-08-23 20:18 ` Re: postgres json: How to query map keys to get children David G Johnston <david.g.johnston@gmail.com>
@ 2014-08-23 23:38 ` Hector Menchaca <hm34306@hotmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Hector Menchaca @ 2014-08-23 23:38 UTC (permalink / raw)
To: David G Johnston <david.g.johnston@gmail.com>; pgsql-sql
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.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-tp581600...
> 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
=
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-08-23 23:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-08-23 18:02 postgres json: How to query map keys to get children Hector Menchaca <hm34306@hotmail.com>
2014-08-23 20:18 ` David G Johnston <david.g.johnston@gmail.com>
2014-08-23 23:38 ` Hector Menchaca <hm34306@hotmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox