Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

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"}

id	jsonb_object_agg
1	{"Case": "Shells", "Color": "Silver"}
2	{"Case": "Shells", "Color": "Red"}