1

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)

1 Answer 1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.