jsonb record object with variable numbre of keys to table-CodePudding

Is it possible to create a query that, given this jsonb field value:

{
  "a": {
    "e1": 10,
    "e2": 30,
    "e3": 50
  },
  "b": {
    "e1": 20,
    "e2": 40,
    "e3": 60
  }
  ...
}

(note that the json object may have a variable number of keys)

returns this table:

e1 | e2 | e3 
--- ---- ----
10 | 30 | 50 
20 | 40 | 60 
...

using the JSON functions and operators available in Postgresql 15?

CodePudding user response：

you can use jsonb_each() to turn the items to rows:

select x.key, 
       x.element ->> 'e1' as e1,
       x.element ->> 'e2' as e2,
       x.element ->> 'e3' as e3
from the_table as t
  cross join jsonb_each(t.the_column) as x(key, element);

Given your sample value, this returns:

key | e1 | e2 | e3
---- ---- ---- ---
a   | 10 | 30 | 50
b   | 20 | 40 | 60