PostgresSQL nested jsonb query for multiple key/value pairs

Question

Starting out with JSONB data type and I'm hoping someone can help me out.

I have a table (properties) with two columns (id as primary key and data as jsonb). The data structure is:

    {
        "ProductType": "ABC",
        "ProductName": "XYZ",
        "attributes": [
            {
            "name": "Color",
            "type": "STRING",
            "value": "Silver"
            },
            {
            "name": "Case",
            "type": "STRING",
            "value": "Shells"
            },
            ...
        ]
    }

I would like to get all rows where an attribute has a specific value i.e. return all rows where Case = 'Shells' and/or Color = 'Red'.

I have tried the following but I'm not able to apply two conditions like Case = 'Shells' and Color = 'Silver'. I can get rows for when a single attributes' name and value matches conditions but I can't figure out how to get it to work for multiple attributes.

---

EDIT 1: I'm able to achieve the results using the following query:

WITH properties AS (
    select *
    from (
        values 
        (1, '{"ProductType": "ABC","ProductName": "XYZ","attributes": [{"name": "Color","type": "STRING","value": "Silver"},{"name": "Case","type": "STRING","value": "Shells"}]}'::jsonb), 
        (2, '{"ProductType": "ABC","ProductName": "XYZ","attributes": [{"name": "Color","type": "STRING","value": "Red"},{"name": "Case","type": "STRING","value": "Shells"}]}'::jsonb)
    ) s(id, data)
)
select 
    *
from (
    SELECT 
        id,
        jsonb_object_agg(attr ->> 'name', attr -> 'value') as aggr
    FROM properties m,
       jsonb_array_elements(data -> 'attributes') as attr
    GROUP BY id
    ) a
where aggr ->> 'Color' = 'Red' and aggr ->> 'Case' LIKE 'Sh%'

I could potentially have millions of these records so I suppose my only concern now is if this is efficient and if not, is there a better way?

Answer 1

step-by-step demo:db<>fiddle

SELECT 
    id
FROM properties m,
   jsonb_array_elements(data -> 'attributes') as attr
GROUP BY id
HAVING jsonb_object_agg(attr ->> 'name', attr -> 'value') @> '{"Color":"Silver", "Case":"Shells"}'::jsonb

The problem is, that jsonb_array_elements() moves both related values into different records. However, this call is necessary to fetch the values. So, you need to reaggregate the values after you were able to read them. This would make it possible to check them in a related manner.

This can be achieved by using the jsonb_object_agg() aggregation function. The trick here is that we create an object with attributes like "name":"value". So, with that, we can easily check if all required attributes are in the JSON object using the @> operator.

---

Concerning "Edit 1"

demo:db<>fiddle

You can do this:

SELECT
    *
FROM ( 
    SELECT 
        id,
        jsonb_object_agg(attr ->> 'name', attr -> 'value') as obj
    FROM properties m,
       jsonb_array_elements(data -> 'attributes') as attr
    GROUP BY id
) s
WHERE obj ->> 'Color' = 'Silver'
    AND obj ->> 'Case' LIKE 'Sh%'

1. Create the new JSON structure as described above for all JSONs
2. Filter this result afterward.

Alternatively you can use jsonb_object_agg() in the HAVING clause as often as you need it. I guess you need to check which way is more performant in your case:

SELECT 
    id
FROM properties m,
   jsonb_array_elements(data -> 'attributes') as attr
GROUP BY id
HAVING 
   jsonb_object_agg(attr ->> 'name', attr -> 'value') ->> 'Color' = 'Silver'
   AND
   jsonb_object_agg(attr ->> 'name', attr -> 'value') ->> 'Case' LIKE 'Sh%'