-
Why golang
sql
andsqlx
libraries can't parsearray_agg
? -
Why golang
json
library parse[null]
as a struct with default values (int = 0
,string = ""
) instead of an error or an empty array ? -
Why postgres
COALESCE(jsonb_agg(DISTINCT to_jsonb(ti)), '[]')
still returns[null]
instead of[]
?
I want to addFILTER…
As always:
"Le context est plus fort que le concept"
"The context is stronger than the concept"
MC Solaar - La Belle et Le Bad Boy
SELECT
t.*,
array_agg(DISTINCT ti.*) AS titles,
array_agg(DISTINCT d.*) AS descriptions
FROM
public.thing t
LEFT JOIN
public.title ti ON t.id = ti.id_thing
LEFT JOIN
public.description d ON t.id = d.id_thing
WHERE
t.id = $1
GROUP BY
t.id;
SELECT
jsonb_build_object(
'thing', to_jsonb(t),
'titles', COALESCE(jsonb_agg(DISTINCT to_jsonb(ti)) FILTER (WHERE ti.id IS NOT NULL), '[]'),
'descriptions', COALESCE(jsonb_agg(DISTINCT to_jsonb(d)) FILTER (WHERE d.id IS NOT NULL), '[]')
) AS result
FROM
public.thing t
LEFT JOIN
public.title ti ON t.id = ti.id_thing
LEFT JOIN
public.description d ON t.id = d.id_thing
WHERE
t.id = $1
GROUP BY
t.id;