postgresqljsonb

PostgreSQL merge several json list


I read a lot about json combine methods but not found my way.

Have this kind of query

WITH
  products AS (
      SELECT * FROM configured_products() p
      WHERE p.is_active
  ),
  products_countries as (
      SELECT countries FROM products
      WHERE products.group_ids @> to_jsonb(121) -- test purpose
      GROUP BY products.countries
  )

The result of SELECT * FROM products_countries is a recordset such as

countries (jsonb)
["BE"]
["FR"]
["FR", "LU"]

Now, I want to merge all these countries in one jsonb record to have something like

countries (jsonb)
["BE", "FR", "LU"]

I tried a lot of solutions such as jsonb_agg() but it returns this

SELECT jsonb_agg(r) FROM (SELECT * FROM products_countries) r
[
  {
    "available_countries": [
      "BE"
    ]
  },
  {
    "available_countries": [
      "FR"
    ]
  },
  {
    "available_countries": [
      "FR",
      "LU"
    ]
  }
]

Is there a simple way to achieve that, without custom function ?


Solution

  • You'll first want to split up the arrays into their elements using jsonb_array_elements in a lateral subquery, then aggregate them back together:

    SELECT jsonb_agg(DISTINCT country)
    FROM configured_products() p,
         jsonb_array_elements(p.countries) AS c(country)
    WHERE p.is_active
      AND p.group_ids @> to_jsonb(121)