[FIXED] How to cast a null value that is returned from a nested select to an empty array?

Issue

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?

Solution

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

Answered By - forpas

Answer Checked By - Cary Denson (PHPFixing Admin)