How to return null if a join has no match, in conjunction with json_build_object?

Question

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?

Answer 1

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);

Answer 2

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