S01E01 why: Golang –> SQL, JSON, postgres

S01E01_why.md

S01E01 Why ?

• Why golang sql and sqlx libraries can't parse array_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 add FILTER…

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

Concept (What I started with)

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;

Context (What I ended up with)

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;