CODEWITHSUNDEEP
ruby-on-railsactiverecord
Code father
Code father

Reputation: 605

How to query json data with hashes inside array in rails?

I have the following set of data on the Note model. Currently, I am using the Postgres for the database. Below are two different records on the Notes table.

id: 1,
name: 'First Note',
items: [{description: 'First Note'}, {description: 'PM1 Second Note'}]

id: 2,
name: 'Second Note',
items: [{description: 'Second Note'}, {description: 'PM2 Second Note'}]

id: 3,
name: 'Third Note',
items: [{description: 'Third Note'}, {description: 'Invalid Second Note'}]

How do I query json field (items) such that I get all the notes with the value PM on the description field i.e the query should return notes with id 1 and 2 from the above example?

Upvotes: 0

Views: 4390

Answers (2)

user11350468
user11350468

Reputation: 1407

With raw SQL you can achieve with the below:

select * from notes,
  json_array_elements(items) element
  where element->>'description' LIKE '%PM%';

Try the below with activerecord:

Note.where("EXISTS (SELECT FROM json_array_elements(items) element WHERE element->>'description' LIKE '%PM%')")

Upvotes: 2

Emu
Emu

Reputation: 5895

I didn't test, but one of the following should work.

Note.where("items->>'description' iLIKE ?", '%PM%')
# or
Note.where("items::json -> 'description' iLIKE ?", '%PM%')

Upvotes: 0

Related Questions