How to query jsonb array field in Rails 5

Question

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}]

Yes I want to query against the JSONB column using Active Record — Kelvin Smith
– Kelvin Smith, Commented Jul 19, 2019 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
– Kelvin Smith, Commented Jul 20, 2019 at 8:18

B-Rad · Accepted Answer · 2020-01-29 20:34:59Z

14

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"}]')

answered Jan 29, 2020 at 20:34

B-Rad

2113 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jan · Accepted Answer · 2021-04-19 12:02:12Z

8

It also works this way:

Route.where('travel_routes @> ?', [{to: "Yaba Lagos"}].to_json)

answered Apr 19, 2021 at 12:02

Jan

16.5k12 gold badges64 silver badges104 bronze badges

Collectives™ on Stack Overflow

How to query jsonb array field in Rails 5

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related