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)
Improve this question
4
  • if you look at the docs edgeguides.rubyonrails.org/…, it looks like you have to do something like: Car.where("components->>'name' = ? AND components->>'maker' = ?", 'engine', 'doc'). Commented Aug 6, 2018 at 21:55
  • Thanks, but I get back #<ActiveRecord::Relation []>. Commented Aug 6, 2018 at 22:05
  • 1
    does this stackoverflow.com/questions/40702813/… help ? Commented Aug 6, 2018 at 22:24
  • 1
    It helps indeed. Car.where('components @> ?', '[{"name": "engine", "maker": "doc" }]') Commented Aug 6, 2018 at 22:33
Related questions
2642
How to show tables in PostgreSQL?
2253
PostgreSQL "DESCRIBE TABLE"
1993
How can I change a PostgreSQL user password?
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.