Postgres JSON Join, Union

Question

I have posts, swipes, notifications. I want to

sort posts by their score
delete swiped ones
join notifications with posts and put joined ones as top scores

So far I have done the first two but couldn't add the notifications as the first elements to the result. This is the working query of the first two.

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

I tried LEFT JOIN for adding notifications but couldn't do it.

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 has a column named data type json. notifications.data->post_id should joined by posts.id with it by score 9999999. where notifications.data->user_id should be equal to 32.

Do you want the notifications as additional columns to the posts, or as additional rows? — Patrick
– Patrick, Commented Aug 7, 2015 at 5:43

Patrick · Accepted Answer · 2015-08-07 06:06:21Z

If you want the rows from table 'notifications' in addition to the rows from table 'posts', then you should 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
LEFT   JOIN posts USING (notifications.data)->>'post_id')
WHERE (data->>'user_id')::int = 32

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

If instead you want to add the columns of table 'notifications' to those of table 'posts, then you should 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;

Collectives™ on Stack Overflow

Postgres JSON Join, Union

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related