LEFT JOIN with `json_build_object` returns JSON object initialized with null values, when it should-CodePudding

Suppose the following,

CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE TABLE IF NOT EXISTS my_schema.city (
    id serial PRIMARY KEY,
    city_name VARCHAR(15) NOT NULL
);
CREATE TABLE IF NOT EXISTS my_schema.user (
    id serial PRIMARY KEY,
    city_id BIGINT REFERENCES my_schema.city (id) DEFAULT NULL
);

INSERT INTO my_schema.city VALUES
    (1, 'Toronto'),
    (2, 'Washington');

INSERT INTO my_schema.user VALUES
    (1);

This is my query:

SELECT
  u.id,
  json_build_object(
    'id', c.id,
    'city_name', c.city_name
  ) as city
FROM my_schema.user u
LEFT JOIN my_schema.city c
   ON c.id = u.city_id

The above returns:

[
    {
        "id": 1,
        "city": {
            "id": null,
            "city_name": null
        }
    }
]

I need the following:

[
    {
        "id": 1,
        "city": null
    }
]

I've tried using this:

SELECT
  u.id,
  COALESCE(json_build_object(
    'id', c.id,
    'city_name', c.city_name
  ) FILTER (WHERE u.city_id IS NOT NULL), 'none') as city
FROM my_schema.user u
LEFT JOIN my_schema.city c
   ON c.id = u.city_id

But that throws an error. How can I achieve this?

CodePudding user response：

You can use CASE:

SELECT
  u.id,
  CASE 
    WHEN c.id IS NOT NULL THEN
      json_build_object(
        'id', c.id,
        'city_name', c.city_name
      ) 
    ELSE NULL
  END as city
FROM my_schema.user u
LEFT JOIN my_schema.city c
   ON c.id = u.city_id

CodePudding user response：

You were including the cityid inside your json object instead of your user id. Was that intentional? Either way, you can change out u.id below with c.id if necessary for the same result. I added an additional record to the dbfiddle to further confirm the output.

dbfiddle

    SELECT json_agg(jsonObjects.obj) 
    FROM (
        SELECT
          json_build_object(
            'id', u.id,
            'city_name',c.city_name
          ) obj
        FROM my_schema.user u LEFT JOIN 
            my_schema.city cON c.id = u.city_id
    ) jsonObjects