postgresql json arrays combine and group and sort

Question

Please suggest me a solution to a certain problem. I have two tables. One is stored on one server, and the other on another. And I need to combine their data on daily statistics once in a while. The tables are identical in fields and structure. But I don't know how to combine the jsonb fields into one array by grouping them by some fields (cash_register field and products->productName field) and calculating the total number. This is what the two tables look like. Please note that the only difference in data will be in column info. And these are the columns that need to be combined correctly. Please help me write such a query. My tables:

+-------------+------------+---------------------------------------------------+
| employee_id |   date     |                        info                       |
+-------------+------------+---------------------------------------------------+
| 2223eb0f0d0x| 01/09/2025 | [
                                {
                                    "cash_register": 1,
                                    "products": [
                                        {
                                            "productName": "name1",
                                            "count": 2
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                },
                                {
                                    "cash_register": 4,
                                    "products": [
                                        {
                                            "productName": "name8",
                                            "count": 12
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                }
                            ]                                |

| 2223eb0f0d0x| 02/09/2025 |   [
                                {
                                    "cash_register": 1,
                                    "products": [
                                        {
                                            "productName": "name1",
                                            "count": 2
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                },
                                {
                                    "cash_register": 3,
                                    "products": [
                                        {
                                            "productName": "name8",
                                            "count": 12
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                }
                            ]     |

| 2223eb0f0d0x| 03/09/2025 |  [
                                {
                                    "cash_register": 2,
                                    "products": [
                                        {
                                            "productName": "name1",
                                            "count": 2
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                },
                                {
                                    "cash_register": 4,
                                    "products": [
                                        {
                                            "productName": "name8",
                                            "count": 12
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                }
                            ]                                               |

| 2223eb0f0d0x| 04/09/2025 |   [
                                {
                                    "cash_register": 1,
                                    "products": [
                                        {
                                            "productName": "name1",
                                            "count": 2
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                },
                                {
                                    "cash_register": 4,
                                    "products": [
                                        {
                                            "productName": "name8",
                                            "count": 12
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                }
                            ]                                               |
+-------------+------------+----------------------------------------------------+

+-------------+------------+--------------------------------------------------+
| employee_id |   date     |                    info                          |
+-------------+------------+--------------------------------------------------+
| 2223eb0f0d0x| 01/09/2025 | [
                                {
                                    "cash_register": 2,
                                    "products": [
                                        {
                                            "productName": "name1",
                                            "count": 2
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                },
                                {
                                    "cash_register": 4,
                                    "products": [
                                        {
                                            "productName": "name8",
                                            "count": 12
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                }
                            ]                                |

| 2223eb0f0d0x| 02/09/2025 |   [
                                {
                                    "cash_register": 1,
                                    "products": [
                                        {
                                            "productName": "name1",
                                            "count": 2
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                },
                                {
                                    "cash_register": 2,
                                    "products": [
                                        {
                                            "productName": "name8",
                                            "count": 12
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                }
                            ]     |

| 2223eb0f0d0x| 03/09/2025 |  [
                                {
                                    "cash_register": 2,
                                    "products": [
                                        {
                                            "productName": "name1",
                                            "count": 2
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                },
                                {
                                    "cash_register": 3,
                                    "products": [
                                        {
                                            "productName": "name8",
                                            "count": 12
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                }
                            ]                                               |

| 2223eb0f0d0x| 04/09/2025 |   [
                                {
                                    "cash_register": 1,
                                    "products": [
                                        {
                                            "productName": "name1",
                                            "count": 2
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                },
                                {
                                    "cash_register": 4,
                                    "products": [
                                        {
                                            "productName": "name8",
                                            "count": 12
                                        },
                                        {
                                            "productName": "name2",
                                            "count": 4
                                        }
                                    ]
                                }
                            ]                                               |
+-------------+------------+----------------------------------------------------+

result table (I made an example for one day, for the rest of the days the algorithm is the same):

+-------------+------------+-------------------------------------------------+
| employee_id |   date     |                     info                        |
+-------------+------------+-------------------------------------------------+
| 2223eb0f0d0x| 01/09/2025 |  [
                                    {
                                        "cash_register": 2,
                                        "products": [
                                            {
                                                "productName": "name2",
                                                "count": 4
                                            },
                                            {
                                                "productName": "name1",
                                                "count": 2
                                            }
                                        ]
                                    },
                                    {
                                        "cash_register": 4,
                                        "products": [
                                            {
                                                "productName": "name8",
                                                "count": 24
                                            },
                                            {
                                                "productName": "name2",
                                                "count": 8
                                            }
                                        ]
                                    },
                                    {
                                        "cash_register": 1,
                                        "products": [
                                            {
                                                "productName": "name2",
                                                "count": 4
                                            },
                                            {
                                                "productName": "name1",
                                                "count": 2
                                            }
                                        ]
                                    }
                                ]                                |

+-------------+------------+-------------------------------------------------+

I am attaching a link to the query - https://dbfiddle.uk/KEAm-g4s

I don't think there's an easy solution in SQL and PostgreSQL JSON operators I give it a try, can you check if this is the expected result: dbfiddle.uk/I31RjYoy — FranckPachot
– FranckPachot, Commented Sep 15 at 13:46

FranckPachot · Accepted Answer · 2025-09-15 20:01:14Z

The SQL/JSON (as of PostgreSQL 16) solution:

WITH all_data AS (  
    SELECT employee_id, date, info FROM my_temp  
    UNION ALL  
    SELECT employee_id, date, info FROM server_table  
),  
-- Flatten registers and products in one pass  
unwound AS (  
    SELECT  
        t.employee_id,  
        t.date,  
        jt.cash_register,  
        jt.productName,  
        jt.count  
    FROM all_data AS t,  
         JSON_TABLE(  
            t.info, '$[*]'  
            COLUMNS (  
                cash_register INT PATH '$.cash_register',  
                NESTED PATH '$.products[*]'  
                COLUMNS (  
                    productName TEXT PATH '$.productName',  
                    count INT PATH '$.count'  
                )  
            )  
         ) AS jt  
),  
-- Sum by employee/date/register/product  
product_totals AS (  
    SELECT  
        employee_id,  
        date,  
        cash_register,  
        productName,  
        SUM(count) AS total_count  
    FROM unwound  
    GROUP BY employee_id, date, cash_register, productName  
),  
-- Regroup products under registers  
register_group AS (  
    SELECT  
        employee_id,  
        date,  
        cash_register,  
        JSON_ARRAYAGG(  
            JSON_OBJECT(  
                'productName': productName,  
                'count': total_count  
            ) ORDER BY productName  
        ) AS products  
    FROM product_totals  
    GROUP BY employee_id, date, cash_register  
),  
-- Regroup registers under info[]  
employee_group AS (  
    SELECT  
        employee_id,  
        date,  
        JSON_ARRAYAGG(  
            JSON_OBJECT(  
                'cash_register': cash_register,  
                'products': products  
            ) ORDER BY cash_register  
        ) AS info  
    FROM register_group  
    GROUP BY employee_id, date  
)  
SELECT *  
FROM employee_group  
ORDER BY date;

For previous versions, see comments

Collectives™ on Stack Overflow

postgresql json arrays combine and group and sort

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