arrays - 如何将从嵌套选择返回的空值转换为空数组？

Question

假设我有以下内容，

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[]);

如果我按如下方式查询，它工作正常，因为至少有一个协作者:

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

但如果我改变

WHERE s.id = 2

到，

WHERE s.id = 1

然后它在 collaborators 字段中返回 null。

我尝试了多种组合，将其转换为 ::BIGINT[] 并使用 NULLIF，但没有任何效果。我在这里做错了什么？

Answer 1

使用 COALESCE():

COALESCE((
    SELECT to_json(array_agg(c.*))
    FROM "user" as c
    WHERE c.id = ANY(s.collaborators)
), to_json(array[]::json[])) as collaborators

参见 demo .

请注意，即使是简单的 '[]' 也可以用于演示目的:

COALESCE((
    SELECT to_json(array_agg(c.*))
    FROM "user" as c
    WHERE c.id = ANY(s.collaborators)
), '[]') as collaborator