PostgreSQL JSON Query with embedded json Object

Question

I'm new to postgres 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

I hope you have a really, really good reason to directly use JSON. Yes, it is a nice feature but if you can you should destructure the data and store it in native rows. If not for performance reasons the at least to keep your sanity. — Martin Thurau, Aug 23 '14 at 16:25
Yep - it's hard to index, and awkward to query. Both are improving, but you shouldn't use json as your first preference data type. PostgreSQL is still mainly a relational database, after all. Speaking of which - *what's your postgresql version*? Given how new the json features are, that's kind of important. — Craig Ringer, Aug 24 '14 at 01:32

score 0 · Answer 1 · answered Aug 24 '14 at 23:02

You can wrap a call to json_object_keys in a sub-query to first get the keys inside "Skill" and then use json_array_elements on the outer query over the result:

SELECT SkillType
, json_array_elements(ResourceDocument->'Skill'->SkillType)->>'Name' AS SkillName 
, json_array_elements(ResourceDocument->'Skill'->SkillType)->>'Rating' AS Rating
, json_array_elements(ResourceDocument->'Skill'->SkillType)->>'Last Used' AS LastUsed
FROM (
    SELECT json_object_keys(resourcedocument->'Skill') AS SkillType, ResourceDocument
    FROM Resource
) t;

PostgreSQL JSON Query with embedded json Object

1 Answers1

Linked