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.

CodePudding user response：

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)