1

In my Car model I have an array of Components stored in a PostgreSQL JSONB field.

A record looks like

id: 1
name: DeLorean
components: [{"name": "engine", "maker": "doc", "scope": "system"}, 
             {"name": "transmission", "maker": "doc", "scope": "subsystem"}]

How can I query the database to fetch only the Cars having a Component with name = engine and maker = doc?

I have tried different things, among which the following but all without success.

Car.where('components @> ?', {name: 'engine', maker: 'doc'}.to_json)
Sig
  • 5,476
  • 10
  • 49
  • 89
  • if you look at the docs https://edgeguides.rubyonrails.org/active_record_postgresql.html#json-and-jsonb, it looks like you have to do something like: `Car.where("components->>'name' = ? AND components->>'maker' = ?", 'engine', 'doc')`. – fanta Aug 06 '18 at 21:55
  • Thanks, but I get back `#`. – Sig Aug 06 '18 at 22:05
  • 1
    does this https://stackoverflow.com/questions/40702813/query-on-postgres-json-array-field-in-rails help ? – fanta Aug 06 '18 at 22:24
  • 1
    It helps indeed. `Car.where('components @> ?', '[{"name": "engine", "maker": "doc" }]')` – Sig Aug 06 '18 at 22:33

0 Answers0