json - Postgres JSON 加入、联合

Question

我有帖子、滑动、通知。我要

1. 按得分对帖子排序
2. 删除刷过的
3. 加入通知与帖子并将加入的通知作为最高分

到目前为止，我已经完成了前两个，但无法将通知作为第一个元素添加到结果中。这是前两个的工作查询。

SELECT posts.id, posts.created_at, posts.data, ((posts.data)->>'score')::NUMERIC as score
FROM  posts
WHERE NOT EXISTS (
    SELECT  *
    FROM    swipes
    WHERE   ((swipes.data)->>'post_id')::INT = posts.id AND ((swipes.data)->>'user_id')::INT = 32)
ORDER BY (data)->>'score' 
LIMIT 5

我尝试使用 LEFT JOIN 添加通知，但无法成功。

SELECT posts.id, posts.created_at, posts.data, ((posts.data)->>'score')::NUMERIC as score
FROM  posts
WHERE NOT EXISTS (
    SELECT  *
    FROM    swipes
    WHERE   ((swipes.data)->>'post_id')::INT = posts.id AND ((swipes.data)->>'user_id')::INT = 32)

-- The part below is new
UNION  ALL
SELECT notifications.id, notifications.created_at, notifications.data, 9999999 AS score
FROM   notifications

--- THIS GIVES ERROR ---
LEFT   JOIN posts USING (notifications.data)->>'post_id') 

WHERE  ((notifications.data)->>'user_id')::INT = 32

-- After join order by score
ORDER BY score 
LIMIT 5

notifications 有一个名为 data 的列，类型为 json。 notifications.data->post_id 应该由 posts.id 加入 9999999。其中 notifications.data->user_id 应该等于32.

Answer 1

如果除了表“posts”中的行之外还需要表“notifications”中的行，那么你应该UNION:

SELECT id, created_at, data, (data->>'score')::numeric AS score
FROM posts
WHERE NOT EXISTS (
    SELECT 1
    FROM swipes
    WHERE ((swipes.data)->>'post_id')::int = posts.id
      AND ((swipes.data)->>'user_id')::int = 32)

UNION  ALL

SELECT id, created_at, data, 9999999 AS score
FROM notifications
<strike>LEFT   JOIN posts USING (notifications.data)->>'post_id')</strike>
WHERE (data->>'user_id')::int = 32

-- After join order by score
ORDER BY score 
LIMIT 5;

如果您想将表“notifications”的列添加到表“posts”的列，那么您应该JOIN:

SELECT p.id, p.created_at, p.data, (p.data->>'score')::numeric AS score
       n.id, n.created_at, n.data 
FROM posts p
JOIN notifications n ON n->>'post_id' = p->>'post_id' -- guessing here
WHERE NOT EXISTS (
    SELECT 1
    FROM swipes
    WHERE ((swipes.data)->>'post_id')::int = p.id
      AND ((swipes.data)->>'user_id')::int = 32)
WHERE (n.data->>'user_id')::int = 32
ORDER BY score 
LIMIT 5;