Menu
I've got two tables, one with a foreign key to the other:
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE project (
project_id INT PRIMARY KEY,
employee_id INT NOT NULL REFERENCES employee(employee_id),
name text NOT NULL
);
I'm trying to query for an employee, and their project (assume only one project is allowed). I'd like the project to appear as a JSON object, if a project exists, otherwise I'd ideally like null.
I've got the following query, which does what I'd like ...
SELECT e.employee_id,
e.name,
row_to_json(p.*) AS project
FROM employee e
LEFT JOIN project p USING(employee_id)
Problem is, I want to selectively choose the columns from the join, so I've adjusted the query to be as follows:
SELECT e.employee_id,
e.name,
json_build_object('name', p.name) AS project
FROM employee e
LEFT JOIN project p USING(employee_id)
The problem with the above query is that if an employee has no matching project, the json_build_object still produces an object with null values, as opposed to just not producing an object. The row_to_json query is able to see that the join has no matching row and so it creates no json object.
Is there any way to achieve what I'm looking for, while being able to build my own JSON object using the columns that I choose?
677
If the key column in either A or B contains NULL, that row will never appear in join results Thanks for this this detailed answer! Found the answer. The reason this was not working is because I was using the AND condition on the JOIN and not in the WHEREclause.
The JOIN or INNER JOIN does not return any non-matching rows at all. It returns only the rows that match in both of the tables you join. If you want to get any unmatched rows, you shouldn’t use it.
This FULL JOIN query will return the following data: Let’s inspect the data a bit. There are rows that are found in both tables. There are, however, rows from the table employee that have no corresponding rows in the table project. You can identify these rows by the NULL values in the column project_name.
The results are the following: As you can see, we are missing a row. The row containing the NULL values did not get picked up by our join. We should have all three rows in the result set. The join statement does not deal with NULL values well when joining. So we can use ISNULL to replace the NULL values with something else.
You can use CASE to examine if the value is null:
SELECT e.employee_id,
e.name,
CASE
WHEN p.name IS NULL THEN NULL
ELSE json_build_object('name', p.name)
END AS project
FROM employee e
LEFT JOIN project p USING(employee_id);
This function can be useful if you intend to repeatedly select different values:
create or replace function to_json_or_null(key text, val text)
returns json language sql as $$
select case
when val is null then null
else json_build_object(key, val)
end
$$;
SELECT e.employee_id,
e.name,
to_json_or_null('name', p.name) AS project
FROM employee e
LEFT JOIN project p USING(employee_id);
Use LEFT JOIN LATERAL and build your custom object in the subquery.
SELECT e.employee_id,
e.name,
p2.project
FROM employee e
LEFT JOIN LATERAL (
SELECT json_build_object('name', p.name) AS project
FROM project AS p
WHERE p.employee_id = e.id
) p2 ON true
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
