PostgreSQL (v9.6) query that filters JSON array elements by key/value

Question

We have a jsonb column with data of the type:

"basket": {
    "total": 6,
    "items": [
        {    "type": "A",    "name": "A",    "price": 1    },
        {    "type": "A",    "name": "B",    "price": 2    },
        {    "type": "C",    "name": "C",    "price": 3    },
    ] 
}

We need to construct few queries that will filter specific elements of the items[] array for SELECT and SUM.

We have PG v9.6 so using jsonb_path_query_array didn't work.

Using basket->'items' @> '{"type":"A"}' works to find all entries that has type-A.

But how do we get subquery to

select only basket items of type-A
sum of prices of items of type-A

Thank you!

Enlico · Accepted Answer · 2021-01-13 20:40:04Z

3

This will select the required items:

select * from jsonb_array_elements('{"basket": 
{
    "total": 6,
    "items": [
        {    "type": "A",    "name": "A",    "price": 1    },
        {    "type": "A",    "name": "B",    "price": 2    },
        {    "type": "C",    "name": "C",    "price": 3    }
    ] 
}}'::jsonb#>'{basket,items}') e(it)
where it->>'type' = 'A';

and this the sum of prices:

select sum(cast(it->>'price' as numeric)) from  jsonb_array_elements('{"basket": 
{
    "total": 6,
    "items": [
        {    "type": "A",    "name": "A",    "price": 1    },
        {    "type": "A",    "name": "B",    "price": 2    },
        {    "type": "C",    "name": "C",    "price": 3    }
    ] 
}}'::jsonb#>'{basket,items}') e(it)
where it->>'type' = 'A';

edited Jan 13, 2021 at 20:40

Enlico

30.2k10 gold badges72 silver badges162 bronze badges

answered Jan 30, 2020 at 7:39

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

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

5 Comments

Ben Over a year ago

What is the name of the e() function? does it create a temp var?

Bjarni Ragnarsson Over a year ago

e() is not a function. "e" is the name of the subquery. In this case I opt to name the returning column in the query as "it". These are just aliases for the values already returned but you always need to name the subqueries in order to be able to refer to it.

Ben Over a year ago

Yes, I understand having to name it to be able to refer to it, I've never seen this syntax though. What's the difference between e(it) or AS it ? both seem to work the same way..

Bjarni Ragnarsson Over a year ago

Hi. You are right. It doesn't matter in this case as the subquery only returns single column of type jsonb object - actually in a column named "value". The value is also accessible as it.value (if written only as it and not e(it)). Just habits kicking in. This is a very useful syntax when the subquery returns many columns. It is like this, e is an alias for the subquery (it could be a table) and it is an alias for the column in the query/table. If many columns it would be e(col1, col2, col3) for example. See here: postgresql.org/docs/11/queries-table-expressions.html

Ben Over a year ago

aaaaaah yes (lightbulb moment) That all makes sense now. Thanks for clarifying the syntax.

Collectives™ on Stack Overflow

PostgreSQL (v9.6) query that filters JSON array elements by key/value

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related