6

How to use active record to query an array of jsonb objects

Schema

 create_table "routes", force: :cascade do |t|
    t.string "state"
    t.text "address"
    t.bigint "user_id", null: false
    t.jsonb "travel_routes", array: true
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["user_id"], name: "index_routes_on_user_id"
  end

Rails Console 



travel_routes: [{"to"=>"Yaba Lagos", "fee"=>5000}, {"to"=>"Lagos Iyanapaja", "fee"=>3000}]
Kelvin Smith
  • 61
  • 1
  • 1
  • 3
  • What you want to query against this JSONB column? – Arup Rakshit Jul 19 '19 at 19:56
  • Yes I want to query against the JSONB column using Active Record – Kelvin Smith Jul 19 '19 at 20:36
  • Are you sure PostgreSQL does not support jsonb Array, because it is accepting my inputs very fine, just querying the data that is a bit of a headache. How would I query a json[] array, any examples you could show me based on the data above. – Kelvin Smith Jul 20 '19 at 08:18

2 Answers2

13

This was answered well in this SO post:

Query on Postgres JSON array field in Rails

For the question in this post, this should work to find where "to" = "Yaba Lagos":

Route.where('travel_routes @> ?', '[{"to": "Yaba Lagos"}]')
B-Rad
  • 201
  • 3
  • 5
7

It also works this way:

Route.where('travel_routes @> ?', [{to: "Yaba Lagos"}].to_json)
Jan
  • 12,992
  • 9
  • 53
  • 89