Postgres Many-to-Many JSON Aggregation

Question

I've been struggling with a query for the past few days and I'm hoping someone can help. I have 3 tables:

CREATE TABLE user (
    ID SERIAL PRIMARY KEY,
    USERNAME TEXT NOT NULL UNIQUE
);

CREATE TABLE skill (
    ID SERIAL PRIMARY KEY,
    NAME TEXT NOT NULL
);

CREATE TABLE user_skill (
    ID SERIAL PRIMARY KEY,
    USER_ID INTEGER REFERENCES user(id),
    SKILL_ID INTEGER REFERENCES skill(id)
);

I am trying to run a query so that I can return a single object which has the JSON format like so:

{
    "id": 1,
    "username": "test123",
    "skills": [{
        "name": "skillOne"
    }, {
        "name": "skillTwo"
    }, {
        "name": "skillThree"
    }]
}

What I have so far looks like this:

SELECT
    json_build_object(
        'id', u.id,
        'username', u.username,
        'skills', jsonb_agg(skill)
    )  
FROM (
    SELECT 
        jsonb_build_object(
            'name', s.name
        ) skill
    FROM user_skill us
    JOIN skill s ON us.skill_id = s.id
) xombi_user u;

EDIT:

I now have this which returns the json array correctly, but now need to build a single json object out of it.

SELECT u.id, u.username, jsonb_agg(s)
    FROM user u
    LEFT JOIN user_skill us ON u.id = us.user_id
    LEFT JOIN skill s ON us.skill_id = s.id
    WHERE u.id = 60
GROUP BY u.id;

invictvs1 · Accepted Answer · 2016-08-25 01:25:33Z

6

Got it! Hope it helps someone:

SELECT 
    json_build_object(
        'id', u.id,
        'username', u.username,
        'skills', jsonb_agg(s)
    ) 
    FROM user u
    LEFT JOIN user_skill us ON u.id = us.user_id
    LEFT JOIN skill s ON us.skill_id = s.id
    WHERE u.id = 60
GROUP BY u.id;

answered Aug 25, 2016 at 1:25

invictvs1

4791 gold badge6 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Postgres Many-to-Many JSON Aggregation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related