[Ruby on Rails] ActiveRecord Postgresql Jsonb & Array Query

ar jsonb & array query.md

Jsonb Query Scopes

Based on [https://github.com/DmitryTsepelev/store_model]

module JsonbQuery
  extend ActiveSupport::Concern

  included do
    # where_jsonb(:info, { name: "John", age: 18..28, date: "< 2020-01-01" })
    scope :where_jsonb, -> (column_name, attributes) do
      query = all
      contains = { }

      attributes.each_pair do |field_name, value|
        next if value.nil?

        if value.is_a?(String) && value[/[><=]/]
          operator, value = value.split
          raise ArgumentError if value.nil?
          query = query.where_jsonb_range(column_name, field_name, operator, value)
        elsif value.is_a?(Range)
          query = query.where_jsonb_range(column_name, field_name, "between", value)
        else
          contains[field_name] = value
        end
      end

      contains.empty? ? query : query.jsonb_contains(column_name, contains)
    end

    # jsonb_contains(:info, { name: "John", age: 18, date: "2020-01-01" })
    # jsonb_contains(:info, { array: [ 3 ] }) => will match { array: [ 2, 3, 1 ] }
    scope :jsonb_contains, -> (column_name, attributes) do
      where("#{column_name} @> (?)::jsonb", attributes.to_json)
    end

    scope :jsonb_excludes, -> (column_name, attributes) do
      where.not("#{column_name} @> (?)::jsonb", attributes.to_json)
    end

    # jsonb_contains(:info, :array, [ 2, 3 ]) => only match { array: [ 2, 3 ] }
    scope :jsonb_exactly_where, -> (column_name, field_name, value) do
      where("(#{column_name} ->> ?)::text = ?", field_name, value.to_s)
    end

    # jsonb_exists(:info, :name)
    scope :jsonb_exists, -> (column_name, field_name, array: false) do
      return where("(#{column_name} ->> ?) IS NOT NULL", field_name) unless array

      where("jsonb_array_length(#{column_name}-> ?) > 0", field_name)
    end

    scope :jsonb_not_exists, -> (column_name, field_name, array: false) do
      base = where("(#{column_name} ->> ?) IS NULL", field_name)
      return base unless array

      base.or where("jsonb_array_length(#{column_name}-> ?) = 0", field_name)
    end

    # jsonb_like(:info, :name, "Jo")
    scope :jsonb_likes, -> (column_name, field_name, value) do
      where("(#{column_name} ->> ?) LIKE ?", field_name, "%#{value}%")
    end

    scope :jsonb_searches, -> (column_name, value) do
      where("#{column_name}::jsonb::text LIKE ?", "%#{value}%")
    end

    # jsonb_order(:info, :age, "asc")
    scope :jsonb_order, -> (column_name, field_name, direction) do
      order(Arel.sql("(#{column_name} -> '#{field_name}') #{direction}"))
    end

    # where_jsonb_range(:info, :age, "between", 18, 28)
    # where_jsonb_range(:info, :date, "<=", "2023-01-01")
    scope :where_jsonb_range, -> (column_name, field_name, operator, *values, type: nil) do
      if values[0].is_a?(Range)
        raise NotImplementedError if values[0].exclude_end?
        values = [ values[0].first, values[0].last ]
      end

      if type.nil?
        type = case values.first
               when Date, Time, %r{[-/]} then "timestamp"
               when Numeric then "float"
               else "float"
               end
      end

      if operator == "between"
        where("(#{column_name} ->> ?)::#{type} BETWEEN ? AND ?", field_name, *values)
      else
        where("(#{column_name} ->> ?)::#{type} #{operator} ?", field_name, values.first)
      end
    end
  end
end

Jsonb key count

# having:
#   user1.languages      # => { zh_cn: "master", de: "beginner" }
#   user1.languages      # => { zh_cn: "master" }
# then:
#   User.languages_count # => { zh_cn: 2, de: 1 }
def self.languages_count
    joins = all.to_sql.match(/(?<=FROM ).*(?= WHERE)/).to_s.presence || table_name
    all.unscope(:joins)
       .select("key")
       .from("#{joins}, jsonb_each(#{table_name}.languages)")
       .where("key != 'null'").group("key").count
  end

HasJsonbColumn marco

module JsonbQuery
  extend ActiveSupport::Concern
  
  # included do ...

  class_methods do
    # @usage
    #  User.has_jsonb_column :info
    #    then
    #  User.info_where(age: 18..28)
    def has_jsonb_column(column_name)
      %i[ where contains excludes exists not_exists likes searches order ].each do |method_name|
        define_singleton_method "#{column_name}_#{method_name}" do |*args, **options|
          scope = method_name == :where ? "where_jsonb" : "jsonb_#{method_name}"
          send(scope, column_name, *args, **options)
        end
      end
    end
  end
end

Array Query

module ArrayQuery
  extend ActiveSupport::Concern

  included do
    scope :where_array, -> (column_name, value) do
      where("#{column_name} && ARRAY[?]", value)
    end

    scope :where_length_of_array, -> (column_name, operator = "=", value) do
      array_dimension = 1
      where("array_length(#{column_name}, #{array_dimension}) #{operator} ?", value)
    end

    scope :where_blank_array, -> (column_name) do
      where(column_name => [ ]).or(
        where(column_name => nil)
      )
    end
  end

  class_methods do
    def sum_array_length(column_name)
      array_dimension = 1
      sum("array_length(#{column_name}, #{array_dimension})")
    end
  end
end