postgresql - 在 PostgreSQL 子查询的 WHERE 子句中使用别名

Question

在 postgresql 中，我有一个包含 name 和 meta_data 列的用户表。 Meta_data 列是 jsonb，我像这样存储数据:

"{"likes": [{"id": "1", "name": "aaa"}, {"id": "2", "name": "bbb"}, {"id": "3", "name": "ccc"}]}"

对于每个赞，我想选择拥有相同赞的用户数。

SELECT jsonb_array_elements(users.meta_data->'likes')->>'id' AS like_id,
(SELECT count(*) FROM users WHERE users.meta_data->'likes'->>'id' = like_id ) AS count_users
FROM users

此外，我还有一个包含 user_id 列的 clickouts 表。我需要具有特定 like_id 的用户的点击次数。

COUNT(clickouts.id) FILTER ( 
    WHERE clickouts.user_id IN 
    (SELECT user_id FROM users WHERE users.meta_data->'likes'->>'id' = like_id)
)

这些查询不起作用。错误信息是:

column "like_id" does not exist.

您对如何实现这一点有什么建议吗？

Answer 1

然后像这样在 where 中使用相同的逻辑:

SELECT jsonb_array_elements(users.meta_data->'likes')->>'id' AS like_id,
(SELECT count(*) FROM users WHERE users.meta_data->'likes'->>'id' = jsonb_array_elements(users.meta_data->'likes')->>'id' ) AS count_users
FROM users

但似乎如果每个 like_id 有超过 1 行，您将有很多重复项，因此您应该使用 group by。

SELECT jsonb_array_elements(users.meta_data->'likes')->>'id' AS like_id,
count(jsonb_array_elements(users.meta_data->'likes')->>'id') AS count_users
FROM users
GROUP BY jsonb_array_elements(users.meta_data->'likes')->>'id'

编辑:

如果你想在内部查询中使用别名，试试这样:

SELECT t.like_id,
       (SELECT count(*) FROM users WHERE users.meta_data->'likes'->>'id' = t.like_id ) AS count_users FROM (
SELECT jsonb_array_elements(users.meta_data->'likes')->>'id' AS like_id,
FROM users) t