How to aggregate array values in JSONB?

Question

I have the following PostgreSQL table:

CREATE TABLE orders
(
    id uuid NOT NULL,
    order_date timestamp without time zone,
    data jsonb
);

Where data contains json documents like this:

{
    "screws": [
        {
            "qty": 1000,
            "value": "Wood screw"
        },
        {
            "qty": 500,
            "value": "Drywall screw"
        },
        {
            "qty": 500,
            "value": Concrete screw"
        }
    ],
    "nails": [
        {
            "qty": 1000,
            "value": "Round Nails"
        }
    ]
}

How do I can get an overall quantity for all types of screws across all orders? Something like this :)

select value, sum(qty) from orders where section = 'screws' group by value;

S-Man · Accepted Answer · 2019-08-27 18:42:32Z

1

I am not quite sure why you are trying to sum up the qty values, because the GROUP BY value makes only sense if there would be several times the same value which can be summed, e.g. if you would have twice the value Wood screw.

Nevertheless, this would be the query:

step-by-step demo:db<>fiddle

SELECT
    elems ->> 'value' AS value,
    SUM((elems ->> 'qty')::int) AS qty
FROM
    orders,
    jsonb_array_elements(data -> 'screws') elems
GROUP BY 1

Expand the screw array into one row per array element by jsonb_array_elements()
Get the qty value by the ->> operator (which gives out a text type) and cast this into type int
If really necessary, aggregate these key/value pairs.

answered Aug 27, 2019 at 18:42

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

How to aggregate array values in JSONB?

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