Обсуждение: postgres json: How to query map keys to get children

Поиск
Список
Период
Сортировка

postgres json: How to query map keys to get children

От
Hector Menchaca
Дата:
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 Technical
Something 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




Re: postgres json: How to query map keys to get children

От
David G Johnston
Дата:
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.

Re: postgres json: How to query map keys to get children

От
Hector Menchaca
Дата:
Perfect your snippet gave me some clues...

It looks as follows:

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