Search code examples
sqlarrayspostgresqljsonbpg

PostgreSQL How to convert JSONB array of arrays to pg array of arrays?


I have options JSONB field:

enter image description here

And I can get JSONB array:

SELECT "options" FROM "products";

->

'[["black", "2"], ["white", "7"]]'::JSONB

But, How can I make array of arrays?

->

ARRAY[ARRAY['black', '2'], ARRAY['white', '7']]

Solution

  • select (
      select array_agg(
        (select array_agg(s) from jsonb_array_elements_text(a) j2(s))
      ) from jsonb_array_elements(options) j1(a)
    ) from products