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]}

CodePudding user response：

You want json_agg instead of array_agg which the Postgres driver knows how to deal with.

query = Tag
  .joins(:member_tags)
  .order(Tag.arel_table[:name].lower)
  .group(:id)
  .select(
    :id,
    :name,
    :member_tags_count,
    Arel::Nodes::NamedFunction.new(
      'json_agg', 
      [MemberTag.arel_table[:member_id]]
    ).as('member_ids')
  )
@tags = Tag.connection.select_all(query.arel).map(&:to_h)

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.

CodePudding user response：

You can use as_json method and change your pluck to select with alias for ids array

@tags = Tag
  .joins(:member_tags)
  .order('LOWER(name)')
  .group(:id)
  .select(
    :id,
    :name,
    :member_tags_count,
    'array_agg(member_tags.member_id) AS member_ids'
  ).as_json

render json: @tags