By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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 properties m,
jsonb_array_elements(data -> 'attributes') as attr
| id | data | value |
|---|---|---|
| 1 | {"attributes": [{"name": "Color", "type": "STRING", "value": "Silver"}, {"name": "Case", "type": "STRING", "value": "Shells"}], "ProductName": "XYZ", "ProductType": "ABC"} | {"name": "Color", "type": "STRING", "value": "Silver"} |
| 1 | {"attributes": [{"name": "Color", "type": "STRING", "value": "Silver"}, {"name": "Case", "type": "STRING", "value": "Shells"}], "ProductName": "XYZ", "ProductType": "ABC"} | {"name": "Case", "type": "STRING", "value": "Shells"} |
| 2 | {"attributes": [{"name": "Color", "type": "STRING", "value": "Red"}, {"name": "Case", "type": "STRING", "value": "Shells"}], "ProductName": "XYZ", "ProductType": "ABC"} | {"name": "Color", "type": "STRING", "value": "Red"} |
| 2 | {"attributes": [{"name": "Color", "type": "STRING", "value": "Red"}, {"name": "Case", "type": "STRING", "value": "Shells"}], "ProductName": "XYZ", "ProductType": "ABC"} | {"name": "Case", "type": "STRING", "value": "Shells"} |
SELECT 4
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
id,
jsonb_object_agg(attr ->> 'name', attr -> 'value')
FROM properties m,
jsonb_array_elements(data -> 'attributes') as attr
GROUP BY id
| id | jsonb_object_agg |
|---|---|
| 1 | {"Case": "Shells", "Color": "Silver"} |
| 2 | {"Case": "Shells", "Color": "Red"} |
SELECT 2
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
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
| id |
|---|
| 1 |
SELECT 1