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 ?
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)