Find model based on jsonb nested data field

Question

I use Ruby on Rails and Postgres 9.5.

In the database I have a table called events.

I want to find all events based on particular value in the data field.

events.data has the following possible json value:

{ 'transition' => { 'from' => 'bacon', 'to' => 'ham' } }

How can I build a query that will find an event with data => transition => from bacon?

Answer 1

Assuming that your model is called Event, you can do it like this:

Event.where("data -> 'transition' ->> ? = ?", 'from', 'bacon')

Here is jsonb operators reference.

This query will return all events where data.transition.from is equal to bacon.

To DRY your queries, you can add it to the repository, e.g.:

# app/repositories/event_repository.rb

module EventRepository
  extend ActiveSupport::Concern

  included do
    scope :where_transition, ->(field, value) { where("data -> 'transition' ->> ? = ?", field, value) }
  end
end

After that include it in your model:

include EventRepository

Then you can use it like this:

Event.where_transition('from', 'bacon')
Event.where_transition('to', 'ham')