LEFT JOIN should return null, but returns object with nested properties that have null values-CodePudding

I have two tables,

CREATE TABLE IF NOT EXISTS my_schema.layout (
    id serial PRIMARY KEY,
    layout_name VARCHAR (50) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS my_schema.page (
    id serial PRIMARY KEY,
    layout_id BIGINT REFERENCES my_schema.layout (id)
);

INSERT INTO my_schema.layout VALUES
    (1 ,'basic'),
    (2 ,'header');
    
INSERT INTO my_schema.page VALUES
    (1, 1),
    (2, null);

As you can note, page record with id 2 has a null value for the column layout_id.

But, when I execute my query, I get the following results:

id  layout
1   {"id" : 1, "layout_name" : "basic"}
2   {"id" : null, "layout_name" : null}

But I except the result output to be:

id  layout
1   {"id" : 1, "layout_name" : "basic"}
2   null

Here's my query:

SELECT
    p.id,
    json_build_object(
        'id', l.id,
        'layout_name', l.layout_name
    ) as layout
FROM my_schema.page p
LEFT JOIN my_schema.layout l
    ON l.id = p.layout_id

I've also tried COALESCE, but that kept throwing an error. What can I do here?

CodePudding user response：

As your keys are identical with the column names, you can simplify this to:

SELECT p.id,
       to_jsonb(l) as layout
FROM my_schema.page p
  LEFT JOIN my_schema.layout l ON l.id = p.layout_id

which will return a NULL value as you expect.

CodePudding user response：

Horse's answer works, but I don't think it is very general. A more general solution is to wrap your function in a CASE:

CASE WHEN l.id is NULL THEN NULL ELSE json_build_object(
     'id', l.id,
     'layout_name', l.layout_name
 ) END as layout