Nested array json queries

nested_array_json_queries.rb

# Here's an example tracking json object with shipment_events
example_tracking_json = {
   "status": "delivered",
   "shipment_events":[
      {
         "name":"BILLING INFORMATION RECEIVED",
         "time":"2015-07-23T17:22:59.000Z", ...
      }, ...
      {
        "name": "THE RECEIVER WAS NOT AVAILABLE AT THE TIME OF THE FIRST DELIVERY ATTEMPT.",
        "time": "2015-07-24T16:24:00.000Z", ...
      }, ...
      {
         "name":"DELIVERED",
         "time":"2015-07-27T18:44:00.000Z", ...
      }
   ], ...
}

# When was the first event?
Shipment.select("(tracking_info#>>'{shipment_events,1,time}')")

# When was the last shipment event (meaning the shipment was finally delivered)?
Shipment.select("(tracking_info->'shipment_events'
                               ->(json_array_length(tracking_info->'shipment_events') - 1))
                ")

# How many shipments have a failed delivery attempt?
Shipment.where("tracking_info->'shipment_events' @>
               '[{\"name\": \"THE RECEIVER WAS NOT AVAILABLE...\"}]'::json").count

# When was the first failed delivery attempt?
Shipment.find_by_sql("
  SELECT tracking_events.info->'time'
  FROM shipments s
  JOIN (
    SELECT
      json_array_elements(tracking_info->'shipment_events') as info,
      number as shipment_number
    FROM shipments where (tracking_info->'shipment_events') IS NOT NULL
  ) as tracking_events
  ON s.number = tracking_events.shipment_number
  WHERE tracking_events.info->>'name' = 'THE RECEIVER WAS NOT AVAILABLE...'
")