return and empty json if subquery is empty

Question

hiho,

I got a small problem with my query if one of the subselects is empty. Small background story, this query schould return all information about a user and so it can be send as a json to angular by rails.

it works perfectly if every person has a role, but if the person doesn't have any role, the whole result of the query is empty

this would be my result:

[{"id":1,"name":"person0","role":[{"id":1,"name":"role0","created_at":"2016-02-17T13:11:08.176Z","updated_at":"2016-02-17T13:11:08.176Z"},{"id":10,"name":"role9","created_at":"2016-02-17T13:11:08.185Z","updated_at":"2016-02-17T13:11:08.185Z"}]}]

and my query looks like this:

Person.find_by_sql ["
SELECT
  p.id,
  p.name,
  json_agg(DISTINCT r) as role
FROM
  people as p, (
    SELECT
      r.id, r.name
    FROM
      people as p
    JOIN
      people_roles as pr
    ON
      pr.person_id = p.id
    JOIN
      roles as r
    ON
      r.id = pr.role_id
    WHERE
      p.id = ?
    ORDER BY
      p.id
  ) AS r
WHERE
  p.id = ?
GROUP BY
  p.id
    ", id, id]

my goal is to get an empty json string for role, if the subquery for roles doesn't find anything

[{"id":1,"name":"person0","role":[{}] ...

I tried something like this:

 COALESCE(json_agg(DISTINCT r), '[{}]'::json) as role,

this doesn't throw an error if there are roles, but it doesn't work either. I hope the description is sufficient.

I also happen to have an ER-model at hand. I left some parts (subselects for skills, languages, shifts) of the query out for the sake of simplicity

I'd also be happy if I would get default values for id and name if nothing was found

[{"id":1,"name":"person0","role":[{"id":0,"name":"empty"..

So I tried to move the coalesce to the select of the of the subquery, but this doesn't make sense at all. the way I understand it coalesce only works if the query returns a result but only 1 column is empty and not if I don't get any result at all

score 1 · Accepted Answer · answered Feb 18 '16 at 20:52

1

Please try LEFT JOIN instead of regular JOIN.

answered Feb 18 '16 at 20:52

ialiashkevich

615
7
8

well, this actuallly works. thanks alot! would you mind explaining why? I'm not sure I understand it. – Jonny Rimek Feb 18 '16 at 20:57
1

LEFT JOIN returns rows from the "people" table even if there are no corresponding rows in the "roles" table. You can read more on difference between INNER JOIN and LEFT JOIN here: http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – ialiashkevich Feb 18 '16 at 21:10

return and empty json if subquery is empty

1 Answers1