Flatten JSON like data in Postgres 11 Database

Question

I have the following data in a Postgres table that I need to flatten out:

data
===============================================================================================
{"Exterior Lights" :["Headlights - Forward Adaptive","Headlights - Laser","Headlights - LED"]}
{"Generic" : ["Launch Control"]} 
{"Mirror" :["Blind Spot Assistant","Door Mirrors - Integrated LED"]}
{"Safety" :["Tyre Pressure Monitoring", "ABS"]}

Ideally the above data it would look like this afterwards:

System                             Type
=======                            ====
Exterior Lights                    Headlights - Forward Adaptive
Exterior Lights                    Headlights - Laser
Exterior Lights                    headlights - LED
Generic                            Launch Control
Mirror                             Blind Spot Assistant
Mirror                             Door Mirror - Integrated LED
Safety                             Tyre Pressure Monitor 
Safety                             ABS

I've tried various combinations of JSON operators but to no avail. Can anyone help?

score 1 · Accepted Answer · 2021-06-09 15:24:34Z

1

You need to first extract the key/value pairs as rows which can be done using jsonb_each(). Then you can use jsonb_array_elements_text() to create a row for each array element:

select x.system, u.type
from the_table t
  cross join jsonb_each(t.data) as x(system, value)
  cross join jsonb_array_elements_text(x.value) as u(type);

Online example

edited Jun 9, 2021 at 15:24

answered Jun 9, 2021 at 14:52

user330315

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

Collectives™ on Stack Overflow

Flatten JSON like data in Postgres 11 Database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related