Suppose I have the following,
CREATE TABLE IF NOT EXISTS my_schema.user (
id serial PRIMARY KEY,
user_name VARCHAR (50) UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS my_schema.project (
id serial PRIMARY KEY,
project_name VARCHAR (50) UNIQUE NOT NULL,
owner BIGINT NOT NULL REFERENCES my_schema.user (id),
collaborators BIGINT[] DEFAULT array[]::bigint[]
);
INSERT INTO my_schema.user VALUES
(1 ,'Mike')
,(2 ,'Peter')
,(3 ,'Roger');
INSERT INTO my_schema.project VALUES
(1 ,'project1', 1, array[2, 3])
,(2 ,'project2', 1, array[]::integer[]);
If I query as follows, it works just fine, because there is at least one collaborator:
SELECT
s.id,
s.project_name,
(
SELECT to_json(array_agg(c.*))
FROM my_schema.user as c
WHERE c.id = ANY(s.collaborators)
) as collaborators,
json_build_object(
'id', u.id,
'user_name', u.user_name
) as user
FROM my_schema.project s
INNER JOIN my_schema.user u
ON s.owner = u.id
WHERE s.id = 2
But if I change
WHERE s.id = 2
to,
WHERE s.id = 1
then it returns null
in the collaborators
field.
I've tried a variety of combinations of casting it to ::BIGINT[]
and using NULLIF
, but nothing worked. What am I doing wrong here?
CodePudding user response:
Use COALESCE()
:
COALESCE((
SELECT to_json(array_agg(c.*))
FROM "user" as c
WHERE c.id = ANY(s.collaborators)
), to_json(array[]::json[])) as collaborators
See the demo.
Note that even a simple '[]'
works for presentation purposes:
COALESCE((
SELECT to_json(array_agg(c.*))
FROM "user" as c
WHERE c.id = ANY(s.collaborators)
), '[]') as collaborator