Flatten Postgres JSONB object containing arrays

Question

I have a jsonb structure like this:

{
  "id": "id1",
  "someObject": {
    "key1": [
       "abc",
       "xyz"
    ],
    "key2": [
       "abc",
       "xyz"
    ]
  }
}

I want to translate it into a row-column representation like below:

|  id  |  keys   |    values   |
| ---- | ------- | ----------- |
| id1  |  key1   |    abc      |
| id1  |  key1   |    xyz      |
| id1  |  key2   |    abc      |
| id1  |  key2   |    xyz      |

I cannot use the keys (like key1 or key2) in the query because they are different across different objects.

I tried using Postgres' jsonb functions but couldn't find a solution.

How can I do this using a short query?

Thanks in advance.

user330315 · Accepted Answer · 2022-08-09 09:48:18Z

2

You can use jsonb_each to iterate over the keys. This can be combined with jsonb_array_elements_text() to get the array elements for each key:

select t.the_column ->> 'id' as id, 
       o.key, 
       x.value
from the_table t
  cross join jsonb_each(t.the_column -> 'someObject') as o(key, element)
  cross join jsonb_array_elements_text(o.element) as x(value)

answered Aug 9, 2022 at 9:48

user330315

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

Collectives™ on Stack Overflow

Flatten Postgres JSONB object containing arrays

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