Extending Arel to support @> operator

01_Annotated_Source.md

I've been doing some work lately with JSON documents in PostgreSQL using jsonb columns. You can query these documents using a GIN index in PG, but the syntax can be a little cumbersome

SELECT "events".* FROM "events" WHERE "events"."body" @> '{"shift":{"user_id":2}}'

You have to construct the right side of the query as a JSON string, which is a bit annoying. So I wondered if I could adapt Arel to do the tedious stuff for me.

Essentially what I'm doing above is a standard infix notation, with a PG-specific operator.

module Arel
  module Nodes
    class Contains < Arel::Nodes::InfixOperation
      def initialize(left, right)
        super(:'@>', left, right)
      end
    end
  end
end

The SQL visitor for PostgreSQL needs to understand this new node, but it doesn't need any special functionality.

module Arel
  module Visitors
    class PostgreSQL < Arel::Visitors::ToSql
      alias_method :visit_Arel_Nodes_Contains, :visit_Arel_Nodes_InfixOperation
    end
  end
end

Finally I need a public API for this. You can query multiple document columns at once, and they will result in separate where queries.

module ActiveRecord::QueryMethods
  def contains(predicate)
    predicates = predicate.map do |column, predicate|
      column    = table[column]
      predicate = column.type_cast_for_database(predicate)
      predicate = Arel::Nodes.build_quoted(predicate)
      
      where Arel::Nodes::Contains.new(column, predicate)
    end

    return none if predicates.length == 0
    predicates.inject(:merge)
  end
end

That applies to scopes, but top-level helpers are delegated here:

module ActiveRecord::Querying
  delegate :contains, :to => :all
end

So let's say I have an Event with a body column containing a json document.

Event.contains(body: { shift: { user_id: 2 } })

SELECT "events".* FROM "events" WHERE "events"."body" @> '{"shift":{"user_id":2}}'

Or, perhaps we have a blog Post with a tags column containing an array of strings

Post.contains(tags: %w[subject-1 subject-3])

SELECT "posts".* FROM "posts" WHERE "posts"."tags" @> '{subject-1,subject-3}'

02_Source.rb

require 'arel/nodes/binary'
require 'arel/visitors/postgresql'
require 'active_record/querying'
require 'active_record/relation/query_methods'

module Arel
  module Nodes
    class Contains < Arel::Nodes::InfixOperation
      def initialize(left, right)
        super(:'@>', left, right)
      end
    end
  end
end

module Arel
  module Visitors
    class PostgreSQL < Arel::Visitors::ToSql
      alias_method :visit_Arel_Nodes_Contains, :visit_Arel_Nodes_InfixOperation
    end
  end
end

module ActiveRecord::QueryMethods
  def contains(predicate)
    predicates = predicate.map do |column, predicate|
      column    = table[column]
      predicate = column.type_cast_for_database(predicate)
      predicate = Arel::Nodes.build_quoted(predicate)
      
      where Arel::Nodes::Contains.new(column, predicate)
    end

    return none if predicates.length == 0
    predicates.inject(:merge)
  end
end

module ActiveRecord::Querying
  delegate :contains, :to => :all
end