CODEWITHSUNDEEP
sqlpostgresql
jmls
jmls

Reputation: 2969

return joins as json with postgres 10

I have the following structure

task -> hasMany links -> hasOne user

what I would like to see returned is

task.id | [
   { linkType: 'foo', userid:1, taskid:1, 
        user : { name: 'jon', lastname: 'smith'}, 
   ... ]

being a sql noob, I have managed to get so far as this

select task.id, json_agg(links.*) as links from task 
    left outer join links on task.id = links.taskid 
    join "user" on "user".id = links.userid 
    group by task.id ;

which gives me

task.id | [{ linkType: 'foo', userid:1, taskid:1}, ... ]

but obviously missing the user

I'm kinda stuck now on how to add the user property to each of the link array items

I've read several documents but they always seem to stop at the first join

the schema design is

create table task (id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY)
create table "user" (id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, username text)

create table links (taskid integer references task (id), userid integer references "user" (id) );

Upvotes: 1

Views: 57

Answers (1)

Dario
Dario

Reputation: 2713

Your example does not reference the table structure you provided, because "user" has a name and a lastname column in the former and a single username column in the latter, but you did provide enough information to give an answer. I am using the two-column user, because it allows to show how to construct nested JSON objects.

SELECT task.id, 
       json_agg(
         json_build_object(
           'linkType', 'foo',
           'taskid', links.taskid, 
           'userid', links.userid, 
           'user', json_build_object('name', u.name, 'lastname', u.lastname)
         )
       ) AS links 
  FROM task 
  LEFT OUTER JOIN links ON task.id = links.taskid 
  JOIN "user" u ON u.id = links.userid 
 GROUP BY task.id;

The general problem of building json objects with the appropriate key values is discussed in this excellent DBA.SE answer. I have adopted what is listed there as solution number 3 because I think it is the most flexible and the most readable. Your tastes might differ.

Upvotes: 1

Related Questions