Nested array json queries with view

nested_array_json_queries_with_view.rb

# Here's the migration to pull each shipment_event into its own row in a view
class CreateTrackingEventsView < ActiveRecord::Migration
  def change
    sql = %{
      SELECT
        json_array_elements(tracking_info->'shipment_events') as info,
        number as shipment_number
      FROM shipments where (tracking_info->'shipment_events') IS NOT NULL
    }
    
    # create_view comes from schema_plus
    create_view 'tracking_events', sql, :force => true
  end
end

# Here's a model to query that view
class TrackingEvent < ActiveRecord::Base
  belongs_to :shipment, foreign_key: :shipment_number, primary_key: :number

  scope :delivered, -> { where("info->>'name' = ?", EVENT_TEXTS[:delivered]) }
  scope :movement, -> { where("info->>'name' IN (?)", EVENT_TEXTS[:movement]) }
  scope :del_attempt, -> { where("info->>'name' IN (?)", EVENT_TEXTS[:del_attempt]) }
end

# When was the first event?
shipment.tracking_events.order("(info->>'time')").first

# When was the last shipment event (meaning the shipment was finally delivered)?
shipment.tracking_events.delivered.first.date

# How many shipments have a failed delivery attempt?
Shipment.joins(:tracking_events).merge(TrackingEvent.first_delivery_attempt).count

# When was the first failed delivery attempt?
shipment.tracking_events.delivery_attempts.first.time