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;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With