I have the query below that almost works: It returns 3 rows one of which should have first_nation populated (other two should be NULL). But they all get the same data for first_nation
. What I need is the person.id
from the outer where to be a part of the WHERE in the inner query but I don't think that's doable. Any help would be appreciated.
Or another way, I'd like the results of the JOIN to be JSON rather than appearing as additional columns.
SELECT person.id,
(
SELECT row_to_json(x)
FROM (
SELECT ref_first_nations_gov.id
FROM ref_first_nations_gov JOIN person ON person.first_nation_id = ref_first_nations_gov.id
WHERE person.application_id = 1 AND person.archived = false
) x
) AS first_nation
FROM person
WHERE application_id = 1 AND archived = false;
EDIT: Sample Data
SELECT id, application_id, first_nation_id FROM person WHERE application_id = 1;
id | application_id | first_nation_id
----+----------------+-----------------
4 | 1 |
1 | 1 |
2 | 1 |
3 | 1 | 1
What the query above gives me:
id | first_nation
----+--------------
4 | {"id":1}
1 | {"id":1}
3 | {"id":1}
What I want
id | first_nation
----+--------------
4 |
1 |
3 | {"id":1}
Even though I don't have how to test this right now, I don't think you need a subquery.
Try something like this.
SELECT p.id, row_to_json(r.id) FROM person p
FULL OUTER JOIN ref_first_nations_gov r on r.id = p.first_nation_id
WHERE p.application_id = 1 AND p.archived = false;