Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();
create table my_temp (employee_id text, date date, info jsonb);
insert into my_temp
values 
(
  '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
  '2025-09-01',
  '[{ "cash_register": 3,
	 "products":[ { "productName": "name1", "count": 2 },      
								 { "productName": "name2", "count": 4 } 
								]}, 
   {"cash_register": 4,
		 "products":[ { "productName": "name8", "count": 12 },      
								 { "productName": "name2", "count": 4 } 
								]}
							  ]'
),
(
  '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
  '2025-09-02',
  '[{ "cash_register": 1,
								 "products":[ { "productName": "name1", "count": 2 },       
								 { "productName": "name2", "count": 4 } 
								]}, 
								{"cash_register": 3,
								 "products":[ { "productName": "name8", "count": 12 },      
								 { "productName": "name2", "count": 4 } 
								] 
							  }]'
),
(
  '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
  '2025-09-03',
  '[{ "cash_register": 2,
								 "products":[ { "productName": "name1", "count": 2 },       
								 { "productName": "name2", "count": 4 } 
								]}, 
								{"cash_register": 4,
								 "products":[ { "productName": "name8", "count": 12 },      
								 { "productName": "name2", "count": 4 } 
								] 
							  }]'
),
(
  '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
  '2025-09-04',
  '[{ "cash_register": 1,
								 "products":[ { "productName": "name1", "count": 2 },       
								 { "productName": "name2", "count": 4 } 
								]}, 
								{"cash_register": 4,
								"products":[ { "productName": "name8", "count": 12 },      
								 { "productName": "name2", "count": 4 } 
								] 
							  }]'
);

create table server_table (employee_id text, date date, info jsonb);
insert into server_table
values 
(
  '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
  '2025-09-01',
  '[{ "cash_register": 2,
								 "products":[ { "productName": "name1", "count": 2 },      
								 { "productName": "name2", "count": 4 } 
								]}, 
								{"cash_register": 4,
								"products":[ { "productName": "name8", "count": 6 },      
								 { "productName": "name2", "count": 8 } 
								] 
							  }]'
),
(
  '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
  '2025-09-02',
  '[{ "cash_register": 1,
								 "products":[ { "productName": "name1", "count": 2 },       
								 { "productName": "name2", "count": 4 } 
								]}, 
								{"cash_register": 4,
								 "products":[ { "productName": "name4", "count": 5 },      
								 { "productName": "name2", "count": 4 } 
								]} 
							  ] '
),
(
  '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
  '2025-09-03',
  '[{ "cash_register": 2,
								 "products":[ { "productName": "name1", "count": 2 },       
								 { "productName": "name2", "count": 4 } 
								]}, 
								{"cash_register": 4,
								 "products":[ { "productName": "name8", "count": 12 },      
								 { "productName": "name2", "count": 4 } 
								] 
							  }]'
),
(
  '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
  '2025-09-04',
  '[{ "cash_register": 1,
								 "products":[ { "productName": "name1", "count": 2 },       
								 { "productName": "name2", "count": 4 } 
								]}, 
								{"cash_register": 4,
								 "products":[ { "productName": "name8", "count": 12 },      
								 { "productName": "name2", "count": 4 } 
								] 
							  }]'
);


version


PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1



CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 4


WITH "union" AS (  
    SELECT employee_id, "date", info  
    FROM my_temp  
    UNION ALL  
    SELECT employee_id, "date", info  
    FROM server_table  
),  
"unwind" AS (  
    SELECT  
        "union".employee_id,  
        "union"."date",  
        (cr_elem->>'cash_register')::int AS cash_register,  
        prod_elem->>'productName' AS product_name,  
        (prod_elem->>'count')::int AS product_count  
    FROM "union"  
    CROSS JOIN LATERAL jsonb_array_elements("union".info) AS cr_elem  
    CROSS JOIN LATERAL jsonb_array_elements(cr_elem->'products') AS prod_elem  
),  
"sum" AS (  
    SELECT  
        employee_id,  
        "date",  
        cash_register,  
        product_name,  
        SUM(product_count) AS total_count  
    FROM "unwind"  
    GROUP BY employee_id, "date", cash_register, product_name  
),  
"group by register" AS (  
    SELECT  
        employee_id,  
        "date",  
        cash_register,  
        jsonb_agg(  
            jsonb_build_object(  
                'productName', product_name,  
                'count', total_count  
            )  
            ORDER BY product_name -- adjust sort if needed  
        ) AS products  
    FROM "sum"  
    GROUP BY employee_id, "date", cash_register  
),  
"group by employee" AS (  
    SELECT  
        employee_id,  
        "date",  
        jsonb_agg(  
            jsonb_build_object(  
                'cash_register', cash_register,  
                'products', products  
            )  
            ORDER BY cash_register  
        ) AS info  
    FROM "group by register"  
    GROUP BY employee_id, "date"  
)  
SELECT *  
FROM "group by employee"  
ORDER BY "date";  


employee_id
date
info


3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-01
[{"products": [{"count": 2, "productName": "name1"}, {"count": 4, "productName": "name2"}], "cash_register": 2}, {"products": [{"count": 2, "productName": "name1"}, {"count": 4, "productName": "name2"}], "cash_register": 3}, {"products": [{"count": 12, "productName": "name2"}, {"count": 18, "productName": "name8"}], "cash_register": 4}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-02
[{"products": [{"count": 4, "productName": "name1"}, {"count": 8, "productName": "name2"}], "cash_register": 1}, {"products": [{"count": 4, "productName": "name2"}, {"count": 12, "productName": "name8"}], "cash_register": 3}, {"products": [{"count": 4, "productName": "name2"}, {"count": 5, "productName": "name4"}], "cash_register": 4}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-03
[{"products": [{"count": 4, "productName": "name1"}, {"count": 8, "productName": "name2"}], "cash_register": 2}, {"products": [{"count": 8, "productName": "name2"}, {"count": 24, "productName": "name8"}], "cash_register": 4}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-04
[{"products": [{"count": 4, "productName": "name1"}, {"count": 8, "productName": "name2"}], "cash_register": 1}, {"products": [{"count": 8, "productName": "name2"}, {"count": 24, "productName": "name8"}], "cash_register": 4}]



SELECT 4


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;  


employee_id
date
info


3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-01
[{"cash_register" : 2, "products" : [{"productName" : "name1", "count" : 2}, {"productName" : "name2", "count" : 4}]}, {"cash_register" : 3, "products" : [{"productName" : "name1", "count" : 2}, {"productName" : "name2", "count" : 4}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 12}, {"productName" : "name8", "count" : 18}]}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-02
[{"cash_register" : 1, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 3, "products" : [{"productName" : "name2", "count" : 4}, {"productName" : "name8", "count" : 12}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 4}, {"productName" : "name4", "count" : 5}]}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-03
[{"cash_register" : 2, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 8}, {"productName" : "name8", "count" : 24}]}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-04
[{"cash_register" : 1, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 8}, {"productName" : "name8", "count" : 24}]}]



SELECT 4


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;  


employee_id
date
info


3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-01
[{"cash_register" : 2, "products" : [{"productName" : "name1", "count" : 2}, {"productName" : "name2", "count" : 4}]}, {"cash_register" : 3, "products" : [{"productName" : "name1", "count" : 2}, {"productName" : "name2", "count" : 4}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 12}, {"productName" : "name8", "count" : 18}]}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-02
[{"cash_register" : 1, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 3, "products" : [{"productName" : "name2", "count" : 4}, {"productName" : "name8", "count" : 12}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 4}, {"productName" : "name4", "count" : 5}]}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-03
[{"cash_register" : 2, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 8}, {"productName" : "name8", "count" : 24}]}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-04
[{"cash_register" : 1, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 8}, {"productName" : "name8", "count" : 24}]}]



SELECT 4


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' VALUE productName,  
                'count' VALUE 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' VALUE cash_register,  
                'products' VALUE products  
            ) ORDER BY cash_register  
        ) AS info  
    FROM register_group  
    GROUP BY employee_id, date  
)  
SELECT *  
FROM employee_group  
ORDER BY date;  


employee_id
date
info


3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-01
[{"cash_register" : 2, "products" : [{"productName" : "name1", "count" : 2}, {"productName" : "name2", "count" : 4}]}, {"cash_register" : 3, "products" : [{"productName" : "name1", "count" : 2}, {"productName" : "name2", "count" : 4}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 12}, {"productName" : "name8", "count" : 18}]}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-02
[{"cash_register" : 1, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 3, "products" : [{"productName" : "name2", "count" : 4}, {"productName" : "name8", "count" : 12}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 4}, {"productName" : "name4", "count" : 5}]}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-03
[{"cash_register" : 2, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 8}, {"productName" : "name8", "count" : 24}]}]

3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9
2025-09-04
[{"cash_register" : 1, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 8}, {"productName" : "name8", "count" : 24}]}]



SELECT 4

employee_id	date	info
3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9	2025-09-01	[{"products": [{"count": 2, "productName": "name1"}, {"count": 4, "productName": "name2"}], "cash_register": 2}, {"products": [{"count": 2, "productName": "name1"}, {"count": 4, "productName": "name2"}], "cash_register": 3}, {"products": [{"count": 12, "productName": "name2"}, {"count": 18, "productName": "name8"}], "cash_register": 4}]
3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9	2025-09-02	[{"products": [{"count": 4, "productName": "name1"}, {"count": 8, "productName": "name2"}], "cash_register": 1}, {"products": [{"count": 4, "productName": "name2"}, {"count": 12, "productName": "name8"}], "cash_register": 3}, {"products": [{"count": 4, "productName": "name2"}, {"count": 5, "productName": "name4"}], "cash_register": 4}]
3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9	2025-09-03	[{"products": [{"count": 4, "productName": "name1"}, {"count": 8, "productName": "name2"}], "cash_register": 2}, {"products": [{"count": 8, "productName": "name2"}, {"count": 24, "productName": "name8"}], "cash_register": 4}]
3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9	2025-09-04	[{"products": [{"count": 4, "productName": "name1"}, {"count": 8, "productName": "name2"}], "cash_register": 1}, {"products": [{"count": 8, "productName": "name2"}, {"count": 24, "productName": "name8"}], "cash_register": 4}]

employee_id	date	info
3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9	2025-09-01	[{"cash_register" : 2, "products" : [{"productName" : "name1", "count" : 2}, {"productName" : "name2", "count" : 4}]}, {"cash_register" : 3, "products" : [{"productName" : "name1", "count" : 2}, {"productName" : "name2", "count" : 4}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 12}, {"productName" : "name8", "count" : 18}]}]
3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9	2025-09-02	[{"cash_register" : 1, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 3, "products" : [{"productName" : "name2", "count" : 4}, {"productName" : "name8", "count" : 12}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 4}, {"productName" : "name4", "count" : 5}]}]
3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9	2025-09-03	[{"cash_register" : 2, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 8}, {"productName" : "name8", "count" : 24}]}]
3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9	2025-09-04	[{"cash_register" : 1, "products" : [{"productName" : "name1", "count" : 4}, {"productName" : "name2", "count" : 8}]}, {"cash_register" : 4, "products" : [{"productName" : "name2", "count" : 8}, {"productName" : "name8", "count" : 24}]}]