0

In PostgreSQL, how do you sum sub-elements of a JSON field?

Given: Customer <--- Order

where Order has a JSON field quantities_per_product that contains the qty and shipped qty for each product of the Order.

I'd like to get a summary per Customer of the quantities for each product for all the customer's orders.

Example:

Customer 1

> Order 1
quantities_per_product
{
    "product_A": {"qty": 10, "shipped": 5}
    "product_B": {"qty": 20, "shipped": 2}
}


> Order 2
quantities_per_product
{
    "product_A": {"qty": 15, "shipped": 8}
    "product_C": {"qty": 7, "shipped": 1}
}


Expected result for Customer 1:

quantities_per_product_summary
{
    "product_A": {"qty": 25, "shipped": 13}
    "product_B": {"qty": 20, "shipped": 2}
    "product_C": {"qty": 7, "shipped": 1}
}

1 Answer 1

1

This query achieves your given objective:

SELECT jsonb_build_object('quantities_per_product'
                        , jsonb_object_agg(k, jsonb_build_object('qty', q, 'shipped', s)))
FROM  (
   SELECT k, sum((v->>'qty')::int) AS q, sum((v->>'shipped')::int) AS s
   FROM   orders o, jsonb_each(o.quantities_per_product) q(k,v)
   GROUP  BY k
   ) sub;

fiddle

The task would be so much simpler and cheaper with a properly normalized DB schema. Storing your orders in JSON format is highly inefficient for storage, and performance. And does not allow to enforce constraints and referential integrity. See:

4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.