0

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?

Code father
  • 585
  • 1
  • 11
  • 23
  • 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 Sep 15 '20 at 10:00
  • See [Postgresql query array of objects in JSONB field](https://stackoverflow.com/questions/28486192/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 Sep 15 '20 at 13:30
  • See also https://www.postgresql.org/docs/9.5/functions-json.html – max Sep 15 '20 at 13:34

2 Answers2

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%')")
user11350468
  • 1,357
  • 1
  • 6
  • 22
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%')
Emu
  • 5,763
  • 3
  • 31
  • 51
  • 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? – Code father Sep 15 '20 at 13:24
  • @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` – januszm Mar 12 '21 at 17:11