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

Question

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?

Its very unclear what you are asking here. Are we talking about data stored in a database? What rdbms? Is this a native JSON/JSONB column or are you using serialize together with a varchar column? Please provide a description of the schema and an example of the actual desired output. — max
– max, Commented Sep 15, 2020 at 10:00
See Postgresql query array of objects in JSONB field. ActiveRecord does not have any built in methods for querying JSON columns so you need to write SQL strings. Its going to be a complex query as you can't just use Postgres built in @> includes operator with like/ilike. If it where me I would just consider creating a separate table instead of using a JSON column as that lets you write a far simpler join query and use indexes. — max
– max, Commented Sep 15, 2020 at 13:30

user11350468 · Accepted Answer · 2020-09-15 14:14:33Z

2

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%')")

edited Sep 15, 2020 at 14:14

answered Sep 15, 2020 at 13:53

user11350468

1,4471 gold badge10 silver badges24 bronze badges

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

1 Comment

Code father Over a year ago

Thanks mate. The activerecord ones worked like a charm.

Emu · Accepted Answer · 2020-09-15 03:04:45Z

0

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%')

answered Sep 15, 2020 at 3:04

Emu

5,9253 gold badges35 silver badges52 bronze badges

2 Comments

Code father Over a year ago

Thanks, Emu for the response, but the above query does not seem to return any result for me. I am using Postgres for the database. Does it make any difference with the above query?

januszm Over a year ago

@Codefather usually this works well with Postgres: where("items ->> 'description' = ?", variable) BUT I never tested it with LIKE and tbh I haven't seen it in the documentation, so you may need to use exact value comparison with =, <> or is

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest