PostgreSQL UNION, but where do the results come from?

Question

I want to do a union with two tables, but I need to know where the results are coming from. Here is my join.

(SELECT  * FROM watchlist_movies WHERE movie_id = $1 and user_id = $2)
UNION ALL
(SELECT* FROM favorite_movies WHERE movie_id = $1 and user_id = $2)

so my goal is return a single array with two objects in them. For example,

[
{watchList: [... results]},
{favorites: [... results]}
]

Is this result possible or would have to make two separate sql statements and pack them in a array in Node.js?

Answer 1

Try:

(SELECT 'Watchlist', a.* FROM watchlist_movies a WHERE movie_id = $1 and user_id = $2)
UNION ALL
(SELECT 'Favorites', b.* FROM favorite_movies b WHERE movie_id = $1 and user_id = $2)