Question 205652 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Querying with ActiveRecord for values inside a JSON attribute using an array of values

Rails 7
MySQL 8

I have a model Author, with an attribute book set as a JSON.

The book JSON structure:

book => {
          'title', 'pages', 'publication_date'}
        }

I have an array of titles:

titles = ['Tree','Ice','Pulp']

I tried:

authors = Author.where("book -> '$.title' = ?", titles)

But it did not work.

I was trying to model it after what works for a non-JSON attribute:

values = [1, 2, 3]
records = Model.where(column_name: values)

Any ideas?

Tags:

2

Answers


  1. 0

    You were so close:

    Author.where("book -> '$.title' IN (?)", titles)
    Login or Signup to reply.
  2. 0

    You cannot use equality operator with array type in psql, you would need to use the IN operator.

    The -> operator returns the value in type json. You need to use ->> to get it in text type. There is no need to use $.title.

    Author.where("book ->> 'title' IN (?)", titles)

    Checkout the postgres json reference https://www.postgresql.org/docs/9.5/functions-json.html

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search