How to combine results from multiple tables with different columns in postgres?

Question

I have the following 2 tables: title and photo. I want to return rows from both of these tables that are part of the same group, ordered by timestamp, and grouped into a JSON array.

Example:

groupId | allitems
--------------------------------------------------------------------------------------
   1    | [{title1}, {photo1}, {photo2}, {photo3}, {title2}, {photo4}, ...}]

We need the following tables:

title table

titleId |     createdDate     | groupId |       text      | ...
---------------------------------------------------------------
1       | 2022-10-01 09:00:00 |    1    | EXAMPLE TITLE 1 | ...
2       | 2022-10-02 08:00:00 |    1    | EXAMPLE TITLE 2 | ...
3       | 2022-10-03 04:00:00 |    2    | EXAMPLE TITLE 3 | ...

photo table

photoId |      timestamp      | groupId |     url       | ...
-------------------------------------------------------------
1       | 2022-10-01 10:00:00 |    1    | http://www... | ...
2       | 2022-10-02 03:00:00 |    1    | http://www... | ...
3       | 2022-10-02 11:00:00 |    1    | http://www... | ...
4       | 2022-10-04 01:00:00 |    2    | http://www... | ...
5       | 2022-10-04 05:00:00 |    2    | http://www... | ...
6       | 2022-10-04 06:00:00 |    2    | http://www... | ...

Result

groupId | allitems
----------------------------------------------------------------------------------------------------
   1    | [{titleId: 1, createdDate: "2022-10-01 09:00:00", groupId: 1, text: "EXAMPLE TITLE 1", ...}, 
        |  {photoId: 1, timestamp: "2022-10-01 10:00:00", groupId: 1, url: "http://www...", ...}, 
        |  {photoId: 2, timestamp: "2022-10-02 03:00:00", groupId: 1, url: "http://www...", ...},
        |  {titleId: 2, createdDate: "2022-10-02 08:00:00", groupId: 1, text: "EXAMPLE TITLE 2", ...}
        |  {photoId: 3, timestamp: "2022-10-02 08:00:00", groupId: 1, url: "http://www...", ...}]
----------------------------------------------------------------------------------------------------
   2    | [{titleId: 3, createdDate: "2022-10-03 04:00:00", groupId: 2, text: "EXAMPLE TITLE 3", ...},
        |  {photoId: 4, timestamp: "2022-10-04 01:00:00", groupId: 2, url: "http://www...", ...},
        |  {photoId: 5, timestamp: "2022-10-04 05:00:00", groupId: 2, url: "http://www...", ...},
        |  {photoId: 6, timestamp: "2022-10-04 06:00:00", groupId: 2, url: "http://www...", ...}]

Each row contains the groupId and all the titles + photos ordered by timestamp, that are in the form of an JSON array.

Any help would be greatly appreciated!

lemon · Accepted Answer · 2023-02-03 02:02:59Z

1

Once you have built your json objects using JSON_BUILD_OBJECT, you can use JSON_AGG to aggregate on your "groupId" value.

You take out both "tab1.createdDate" and "tab2.timestamp" to take care of the order of your elements inside the array, during the aggregation, using an ORDER BY clause.

WITH cte AS (
    SELECT groupId,
           createdDate AS date_,
           JSON_BUILD_OBJECT('titleId'    , titleId,
                             'createdDate', createdDate,
                             'groupId'    , groupId,
                             'text'       , text_)    AS json_obj
    FROM tab1

    UNION ALL 

    SELECT groupId,
           timestamp_,
           JSON_BUILD_OBJECT('photoId'  , photoId,
                             'timestamp', timestamp_,
                             'groupId'  , groupId,
                             'url'      , url)       AS json_obj
    FROM tab2
)
SELECT groupId, 
       JSON_AGG(json_obj ORDER BY date_) AS all_items
FROM cte
GROUP BY groupId

Check the demo here.

edited Feb 3, 2023 at 2:02

answered Feb 2, 2023 at 21:11

lemon

15.7k6 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aditya Mohile Over a year ago

Appreciate the quick response @lemon! This query works off the assumption that there will only be 1 title in a group, but I just updated the example tables to where now the result has multiple titles for group 1. Is there a way to update this query to sort the combined rows by timestamp / createdDate?

lemon Over a year ago

The idea is to use "tab1.createdDate" and "tab2.timestamp" before the aggregation to give an order to the elements during the aggregation. We can do it as long as JSON_AGG gives the possibility of a row ordering. Got both the answer and the link at the bottom of the answer updated accordingly.

Collectives™ on Stack Overflow

How to combine results from multiple tables with different columns in postgres?

We need the following tables:

Result

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

We need the following tables:

Result

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related