CREATE TABLE my_temp (
    employee_id TEXT,
    date DATE,
    info JSONB
);

INSERT INTO my_temp (employee_id, date, info)
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 }
          ]
        }
     ]'
);

db.my_temp.insertMany([
  {
    employee_id: "3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9",
    date: ISODate("2025-09-01"),
    info: [
      {
        cash_register: 3,
        products: [
          { productName: "name1", count: 2 },
          { productName: "name2", count: 4 }
        ]
      },
      {
        cash_register: 4,
        products: [
          { productName: "name8", count: 12 },
          { productName: "name2", count: 4 }
        ]
      }
    ]
  }
]);

db.my_temp.aggregate([
  { $unionWith: { coll: "server_table" } },
  { $unwind: "$info" },
  { $unwind: "$info.products" },
  { $group: {
      _id: {
        employee_id: "$employee_id",
        date: "$date",
        cash_register: "$info.cash_register",
        productName: "$info.products.productName"
      },
      total_count: { $sum: "$info.products.count" }
    }
  },
  { $group: {
      _id: {
        employee_id: "$_id.employee_id",
        date: "$_id.date",
        cash_register: "$_id.cash_register"
      },
      products: {
        $push: {
          productName: "$_id.productName",
          count: "$total_count"
        }
      }
    }
  },
  { $project: { _id: 0, employee_id: "$_id.employee_id", date: "$_id.date", info: 1 } },
  { $sort: { date: 1 } }
]);

WITH
all_data AS (
    SELECT employee_id, "date", info FROM my_temp
    UNION ALL
    SELECT employee_id, "date", info FROM server_table
),
unwound AS (
    SELECT
        ad.employee_id,
        ad.date,
        (reg_elem->>'cash_register')::int AS cash_register,
        prod_elem->>'productName' AS product_name,
        (prod_elem->>'count')::int AS product_count
    FROM all_data ad
    CROSS JOIN LATERAL jsonb_array_elements(ad.info) AS reg_elem
    CROSS JOIN LATERAL jsonb_array_elements(reg_elem->'products') AS prod_elem
),
product_totals AS (
    SELECT
        employee_id,
        date,
        cash_register,
        product_name,
        SUM(product_count) AS total_count
    FROM unwound
    GROUP BY employee_id, date, cash_register, product_name
)
SELECT *
FROM product_totals
ORDER BY date;