Querying a JSON field to get product variants by variant type

Question

SO i am developing an Ecommerce application and i have a table called product variants in which i am storing product variants dynamically using jsonb datatype.My data is being stored like this!

variants: {"size": "20","color": "red"},{"size": "30","color": "yellow"}

I would like to query the variants in such a way that i get all the values of a particular variant for a particular product like this:

size: ["20","30"],color: ["red","yellow"]

I have tried a lot of things but have not been able to get my desired result!

Answer 1

Converting JSONB from

[{"size": "20", "color": "red"}, {"size": "30", "color": "yellow"}]

to

{"size": ["20", "30"], "color": ["red", "yellow"]}

PostgreSQL query:

select jsonb_object_agg(a, bs) from (
    select a, jsonb_agg(b) as bs from (
        select (jsonb_each(o)).* from (
            select v from jsonb_array_elements(
                '[{"size": "20", "color": "red"}, {"size": "30", "color": "yellow"}]'::jsonb
            ) t(v)
        ) t(o)
    ) tt(a, b)
    group by a
) ttt