Rails PSQL JSON query: find the nested array element at unknown position?

Question

Please see an example here: http://edgeguides.rubyonrails.org/active_record_postgresql.html#json

How do I get all the records containing "jack" inside change array?

I can currently do it with such a query:

Event.where("payload #>> '{change,0}' = 'jack' ")

But what if I don't know the exact "jack"'s position (0 in this case) inside change array? How to rearrange the Rails PSQL query then?

Answer 1

Take a look at Query for array elements inside JSON type, especially discussion of the @> (contains) operator (if you're using a jsonb column).