How to group by values of a json field that keys are dynamic?-CodePudding

I have a field that is comma separated string and another JSON field that its keys are coming from values of comma separated field.

Now I want to group by, by each value in JSON field.

How can I do that?

This is a sample data:

fieldA   fieldB
a,b      {'a': 'value1', 'b': 'value3'}
c,d      {'c': 'value3', 'd': 'value4'}

Actually, I want to group by value1 value2 value3

And output I need is like this :

       fieldx    fieldy
row1:  value1     a
row2:  value3     b,c
row3:  value4     d

CodePudding user response：

If the data type of field_b is json, we can use json_each_text() to transform it into (key, value) dataset as below.

with cte as (
select x.key, x.value
from table_o t,
  lateral (select * from json_each_text(t.field_b)) x)
select value as fieldx,
       string_agg(key,',') as fieldy
  from cte
 group by fieldx
 order by fieldx;