0

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
S-Man
  • 22,521
  • 7
  • 40
  • 63
hm34306
  • 1
  • 1
  • 1
    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

1 Answers1

0

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;
MatheusOl
  • 10,870
  • 3
  • 30
  • 28