PostgreSQL json(b)_to_record with dynamically named keys?

Question

I have a table with, among other fields, a jsonb field "translations" containing translations and some other properties, there can be any number of properties on it. It's shaped like this:

{
        "nl": {
            "name": "Banaan",
            "description": "<p>Geel fruit</p>"
        },
        "en": {
            "name":"Banana",
            "description": "<p>Yellow fruit</p>",
            "morenested": {"a": "b"} },
            "somethingelse": true
        }
}

I'd like to select this data so that I have this output:

 | lang | data                                               |
 | ---- | -------------------------------------------------- |
 | nl   | {"name": "Banaan","descri... (the entire object) } |
 | en   | {"name": "Banana","descri... (the entire object) } |

I was struggeling with jsonb_to_record, but failed, as it seems that the key value must be specified, which in this case is dynamic; this doesn't work:

SELECT jsonb_to_record(translations) AS x(lang text, data jsonb) FROM mytable;

I'm in the process of designing this table, if it is better to shape it differently, then that's okay. I recon the following could perhaps be easier to convert? Downside here is that this shape does not guarantee me that there are no duplicate translations entries, so I'd rather go with the first option.

[
        {
            "lang": "nl",
            "name": "Banaan",
            "description": "<p>Geel fruit</p>"
        },
        {
            "lang": "en",
            "name":"Banana"
            "description": "<p>Yellow fruit</p>",
            "morenested": {"a": "b"} }
        }
]

I'm on PostgreSQL 14/15 (soonish)

Answer 1

Use jsonb_each, it returns a recordset, which is something you can then SELECT from or JOIN with, like so:

SELECT key AS lang
     , value AS data
  FROM myTable
     , jsonb_each(translations)