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
You can use CASE:
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