skip to Main Content

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?

2

Answers


  1. 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
    
    Login or Signup to reply.
  2. 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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search