how to query json data in text field rails postgresql

Question

I have a database table column(collection) with data type text

I have inserted json data in collection

This is my json data

{"name":"test","age":"25","country":"xxx"}

But now how do i query this json data. Is there any option as follows

User.where(collection: :name) # Here name denotes test

Answer 1

Since JSON is a string, you can only make string comparisons using SQLite/MySQL(or any other database). Also, try to keep datatype as binary.

You can perform: LIKE operation for occurrence of sub-string inside your stored JSON string.

The above comparison you are doing will not work as it is meant for searching only name in collection column whereas you have stored a whole JSON string in that column.