问题

假设以下内容，

```sql
CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE TABLE IF NOT EXISTS my_schema.user (
    id serial PRIMARY KEY,
    chat_ids BIGINT[] NOT NULL
);

CREATE TABLE IF NOT EXISTS my_schema.chat (
    id serial PRIMARY KEY,
    chat_id_value BIGINT UNIQUE NOT NULL
);

INSERT INTO my_schema.chat VALUES
    (1, 12321);

INSERT INTO my_schema.user VALUES
    (1, '{12321}');

当我查询一个不存在的聊天记录的用户时，我仍然收到结果：

SELECT u.id,
	(
		SELECT TO_JSON(COALESCE(ARRAY_AGG(c.*) FILTER (WHERE c IS NOT NULL), '{}'))
		FROM my_schema.chat as c
		WHERE c.chat_id_value = ANY (ARRAY[ 1234 ]::int[])
	) AS chat_ids
FROM my_schema.user as u

显然，没有与 chat_id_value = 1234 的 my_schema.chat 记录。

我尝试添加，

. . .
FROM my_schema.user as u
WHERE chat_ids != '{}'

但这仍然产生相同的结果：

[
    {
        "id": 1,
        "chat_ids": []
    }
]

我尝试 WHERE ARRAY_LENGTH(chat_ids, 1) != 0，WHERE CARDINALITY(chat_ids) != 0，都没有返回预期的结果。

奇怪的是，WHERE ARRAY_LENGTH(chat_ids, 1) != 1 可以工作，这意味着 chat_ids 的长度为 1，而实际上它是 0？非常令人困惑。

我在这里做错了什么？预期的结果应该是 []。

<details>
<summary>英文:</summary>

Suppose the following,

CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE TABLE IF NOT EXISTS my_schema.user (
id serial PRIMARY KEY,
chat_ids BIGINT[] NOT NULL
);

CREATE TABLE IF NOT EXISTS my_schema.chat (
id serial PRIMARY KEY,
chat_id_value BIGINT UNIQUE NOT NULL
);

INSERT INTO my_schema.chat VALUES
(1, 12321);

INSERT INTO my_schema.user VALUES
(1, '{12321}');

When I query for a user record with a nonexisting chat, I still receive a result:

SELECT u.id,
(
SELECT TO_JSON(COALESCE(ARRAY_AGG(c.*) FILTER (WHERE c IS NOT NULL), '{}'))
FROM my_schema.chat as c
WHERE c.chat_id_value = ANY (ARRAY[ 1234 ]::int[])
) AS chat_ids
FROM my_schema.user as u

Clearly, there is no `my_schema.chat` record with with `chat_id_value = 1234`.

I've tried adding,

. . .
FROM my_schema.user as u
WHERE chat_ids != '{}'

But this still yields the same result:

[
{
"id": 1,
"chat_ids": []
}
]

I've tried `WHERE ARRAY_LENGTH(chat_ids, 1) != 0`, `WHERE CARDINALITY(chat_ids) != 0`, none return the expected result.

Oddly enough, `WHERE ARRAY_LENGTH(chat_ids, 1) != 1` works, implying the length of `chat_ids` is `1` when it's actually `0`? Very confusing.

What am I doing wrong here? The expected result should be `[]`.

</details>


# 答案1
**得分**: 3

如果在 `my_schema.chat` 上的子查询没有返回结果，你将会得到 NULL，而 `coalesce` 会将其转换为 `{}`。此外，内部查询与外部查询没有关联，因此在 `my_schema."user"` 的每一行中都会得到相同的结果。你应该使用内连接：

```sql
SELECT u.id,
       TO_JSON(COALESCE(ARRAY_AGG(c.*) FILTER (WHERE c IS NOT NULL), '{}'))
FROM my_schema.user as u
   JOIN my_schema.chat as c
      ON c.chat_id_value = ANY (u.chat_ids);

我认为你的数据模型不太好。你应该避免使用数组，而是使用联接表。这将提供更好的性能和更简单的查询。

SELECT u.id,
       TO_JSON(COALESCE(ARRAY_AGG(c.*) FILTER (WHERE c IS NOT NULL), '{}'))
FROM my_schema.user as u
   JOIN my_schema.chat as c
      ON c.chat_id_value = ANY (u.chat_ids);

答案2

得分: 1

你可以按以下方式操作：

WITH cte as (
  SELECT TO_JSON(ARRAY_AGG(c.*) FILTER (WHERE c IS NOT NULL)) as to_json
  FROM my_schema.chat as c
  inner join  my_schema.user u on c.chat_id_value = ANY (u.chat_ids)
  WHERE c.chat_id_value = ANY (ARRAY[ 12321]::int[])
)
select * 
from cte where to_json is not null;

这将在查询不匹配时强制不显示任何结果！

演示在这里

WITH cte as (
  SELECT TO_JSON(ARRAY_AGG(c.*) FILTER (WHERE c IS NOT NULL)) as to_json
  FROM my_schema.chat as c
  inner join  my_schema.user u on c.chat_id_value = ANY (u.chat_ids)
  WHERE c.chat_id_value = ANY (ARRAY[ 12321]::int[])
)
select * 
from cte where to_json is not null;