Fetch raw JSON from PostgreSQL

json_aggregate_helper.rb

module JsonAggregateHelper
  def to_json_agg_qeury(input, raw=false)
    return "select json_agg(t) from (#{input}) t;" if raw

    [ActiveRecord::Relation, ActiveRecord::AssociationRelation].each do |type|
      return "select json_agg(t) from (#{input.to_sql}) t;" if input.class.ancestors.include?(type)
    end
    raise ArgumentError.new("#{__method__} input class '#{input.class.to_s}' is the wrong type")
  end

  def fetch_json_agg(input, raw=false)
    ActiveRecord::Base.connection.exec_query(to_json_agg_qeury(input, raw))[0].fetch('json_agg')
  end

  def build_query(base_table, associations)
    assoc_sql = ''
    associations.each_with_index do |assoc_data,idx|
      assoc_sql << ',' if idx > 0
      assoc_sql << """( select json_agg(tmp_#{assoc_data[:table_name]})
      from (
          select * from #{assoc_data[:table_name]} where #{assoc_data[:table_name]}.#{assoc_data[:assoc_column]} = base_table.id
      ) tmp_#{assoc_data[:table_name]}
      ) as #{assoc_data[:table_name]}
      """
    end
    "select json_agg(t)
       from (
        select *, #{assoc_sql} from #{base_table} as base_table
    ) t;"
  end
end

# For example:
json_string_from_rails = fetch_json_agg( User.where(id: 1) )
# Or
json_string_from_raw   = fetch_json_agg('select * from users', true)

# For nested associations
sql = build_query('users', [ 
  {table_name: 'items', assoc_column: 'user_id'},
  {table_name: 'searches',  assoc_column: 'user_id'}
  ]
)
nested_json = ActiveRecord::Base.connection.exec_query(sql)