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

Question

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

I edited my question, I should use : instead of ,

Obtice
– Obtice

2022-12-11 05:45:32 +00:00
Commented Dec 11, 2022 at 5:45 — Obtice
– Obtice, Commented Dec 11, 2022 at 5:45

JHH · Accepted Answer · 2022-12-11 07:18:55Z

1

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;

answered Dec 11, 2022 at 7:18

JHH

1,5591 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JHH Over a year ago

@Obtice Could you please create another question with the new table schema, sample data and expected output like this one? It will help to understand the question and to come out a correct answer. Thank you

Obtice Over a year ago

you mean maybe this answer is wrong ?

JHH Over a year ago

@Obtice I meant the new request about adding a new min(priority) field from table_o from your first comment, but it's disappeared. I was probably daydreaming. LoL

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related