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
    *
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%'


id
obj


1
{"Case": "Shells", "Color": "Silver"}



SELECT 1


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'
   AND
   jsonb_object_agg(attr ->> 'name', attr -> 'value') ->> 'Case' LIKE 'Sh%'


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