Extension for handling json_agg (postgres) or json_arrayagg (mysql)?

[coleshaw]

Hi! I am wondering if there is a way to aggregate data into an array, like string_agg, but without concatenating? I saw the array_agg extension, but that seems targetted at FK columns that are already arrays of FKs ... and the generated SQL seems different. An example query in Postgres might look like:

SELECT m.name as m_name, json_agg(distinct v.name) as v_name
    FROM monsters as m
    LEFT OUTER JOIN victims as v
    ON (m.id = v.monster_id)
    GROUP BY m_name

And the output might look something like:

[{:m_name=>"monster1", :v_name=>["victim1", "victim2"]},
 {:m_name=>"monster2", :v_name=>["victim3", "victim4"]}]

Thanks!

[jeremyevans]

The related part could be: Sequel.function(:json_agg, Sequel[:v][:name]).distinct.as(:v_name)

[coleshaw]

Ah, thanks! I will give that a try 👍