This is my query within my controller (index action):
@tags = ActiveRecord::Base.connection.execute(
<<~SQL
SELECT t.id, t.name, t.member_tags_count, (
SELECT
json_agg(mt.member_id) as member_ids
FROM member_tags mt
WHERE mt.tag_id = t.id)
FROM tags t
ORDER BY LOWER(t.name)
SQL
)
render json: @tags
It runs in 1.9ms and returns the following:
#<PG::Result:0x000000010e368580 status=PGRES_TUPLES_OK ntuples=31 nfields=4 cmd_tuples=31>
(ruby) @tags.first
{"id"=>1, "name"=>"Avengers", "member_tags_count"=>3, "member_ids"=>"[1, 3, 7]"}
Problem: the member_ids
should be an array of integers for an API, but it is currently returning as a string.
Question: is there a way to return member_ids
as an array without looping through the @tags
result to JSON.parse
it?
Below is my current implementation so I can move on, but it seems messy and takes 4x longer (5.7ms) to run.
@tags = Tag
.joins(:member_tags)
.order('LOWER(name)')
.group(:id)
.pluck(
:id,
:name,
:member_tags_count,
'array_agg(member_tags.member_id)'
).map do |column| {
id: column[0],
name: column[1],
member_tags_count: column[2],
member_ids: column[3]
}
end
render json: @tags
The above returns:
(ruby) @tags.first
{:id=>1, :name=>"Avengers", :member_tags_count=>3, :member_ids=>[1, 3, 7]}
2
Answers
You want
json_agg
instead ofarray_agg
which the Postgres driver knows how to deal with.There also isn’t really any point in using
.pluck
instead of.select_all
as it forces you to have to go through the mess of dealing with arrays.You can use
as_json
method and change yourpluck
toselect
with alias for ids array