Flatten nested JSON structure in PostgreSQL

Question

I'm trying to write a Postgres query that will output my json data in a particular format.

JSON data structure

{
    user_id: 123,
    data: {
        skills: {
            "skill_1": {
                "title": "skill_1",
                "rating": 4,
                "description": 'description text'
            },
            "skill_2": {
                "title": "skill_2",
                "rating": 2,
                "description": 'description text'
            },
            "skill_3": {
                "title": "skill_3",
                "rating": 5,
                "description": 'description text'
            },
            ...
        }
    }
}

This is how I need the data to be formatted in the end:

[
    {
        user_id: 123,
        skill_1: 4, 
        skill_2: 2, 
        skill_3: 5, 
                    ... 
    },
    {
        user_id: 456,
        skill_1: 1, 
        skill_2: 3, 
        skill_3: 4, 
                    ... 
    }
]

So far I'm working with a query that looks like this:

SELECT
    user_id,
    data#>>'{skills, "skill_1",  rating}' AS "skill_1",
    data#>>'{skills, "skill_2",  rating}' AS "skill_2",
    data#>>'{skills, "skill_3",  rating}' AS "skill_3"
FROM some_table

There has to be a better way to go about writing my query. There are 400+ rows and 70+ skills. My above query is a little crazy. Any guidance or help would be greatly appreciated.

Some things to note:

Users rated themselves on 70+ skills
Each skill object has the same structure
Each user rated themselves on the exact same set of skills

In your data structure I am missing the user_id field.

S-Man
– S-Man

2018-08-15 13:48:25 +00:00
Commented Aug 15, 2018 at 13:48 — S-Man
– S-Man, Commented Aug 15, 2018 at 13:48
Updated: They are on the same level as the data object.

MrDevinB
– MrDevinB

2018-08-15 14:44:20 +00:00
Commented Aug 15, 2018 at 14:44 — MrDevinB
– MrDevinB, Commented Aug 15, 2018 at 14:44

S-Man · Accepted Answer · 2018-08-15 19:06:25Z

11

db<>fiddle

I expanded your test data to (note the array around all users):

[{
    "user_id": 123,
    "data": {
        "skills": {
            "skill_1": {
                "title": "skill_1",
                "rating": 4,
                "description": "description text"
            },
            "skill_2": {
                "title": "skill_2",
                "rating": 2,
                "description": "description text"
            },
            "skill_3": {
                "title": "skill_3",
                "rating": 5,
                "description": "description text"
            }
        }
    }
},
{
    "user_id": 456,
    "data": {
        "skills": {
            "skill_1": {
                "title": "skill_1",
                "rating": 1,
                "description": "description text"
            },
            "skill_2": {
                "title": "skill_2",
                "rating": 3,
                "description": "description text"
            },
            "skill_3": {
                "title": "skill_3",
                "rating": 4,
                "description": "description text"
            }
        }
    }
}]

The query:

SELECT 
    jsonb_pretty(jsonb_agg(user_id || skills))               -- E
FROM (
    SELECT
        json_build_object('user_id', user_id)::jsonb as user_id,  -- D
        json_object_agg(skill_title, skills -> skill_title -> 'rating')::jsonb as skills
    FROM (
        SELECT 
            user_id,
            json_object_keys(skills) as skill_title,         -- C
            skills
        FROM (
            SELECT
                (datasets -> 'user_id')::text as user_id,
                datasets -> 'data' -> 'skills' as skills     -- B
            FROM (
                SELECT 
                  json_array_elements(json) as datasets      -- A
                FROM (
                  SELECT '/* the JSON data; see db<>fiddle */'::json
                )s
            )s
        )s  
    )s    
    GROUP BY user_id
    ORDER BY user_id
)s

A Make all array elements ({user_id: '42', data: {...}}) one row each

B First column safe the user_id. The cast to text ist necessary for the GROUP BY later which cannot group JSON output. For the second column extract the skills data of the user

C Extract the skill titles for using them as keys in (D.1).

D.1 skills -> skill_title -> 'rating' extracts the rating value from each skill

D.2 json_object_agg aggregates the skill_titles and each corresponding rating value into one JSON object; grouped by the user_id

D.3 json_build_object makes the user_id a JSON object again

E.1 user_id || skills aggregates the two json object into one

E.2 jsonb_agg aggregates these json objects into an array

E.3 jsonb_pretty makes the result looking pretty.

Result:

[{
    "skill_1": 4,
    "skill_2": 2,
    "skill_3": 5,
    "user_id": "123"
},
{
    "skill_1": 1,
    "skill_2": 3,
    "skill_3": 4,
    "skill_4": 42,
    "user_id": "456"
}]

answered Aug 15, 2018 at 19:06

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MrDevinB Over a year ago

This is truly amazing. Should this work in version 9.6.6? I should have added that to my original question. Sorry about that.

S-Man Over a year ago

Yes it works for Postgres 9.6 sqlfiddle.com/#!17/9eecb/19326 Please don't forget to accept and upvote.

MrDevinB Over a year ago

Thank you, thank you, thank you. This is exactly what I needed. You must be a postgres wizard!!! Your magic is very strong.

S-Man Over a year ago

:D. Maybe a little bit :) No, fun! It is just about reading the docs postgresql.org/docs/current/static/functions-json.html and then trial and error until it works.

Collectives™ on Stack Overflow

Flatten nested JSON structure in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related