We recently created a table with JSON field in our Rails app. When the time comes that we need to query the table to find the record with the same JSON field, we find it didn't work as we expected.

Suppose the table name is filter_sets and the field named filters. The following query:

FilterSet.where("filters = ?", params[:filters])

returns error:

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: json = unknown


HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

It turns out that there are JSON and JSONB fields in postgres. Here is the description from official docs regarding the difference:

"The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage."

Once we update the field to JSONB type and then using either

FilterSet.where("filters @> ?", params[:filters])

or

FilterSet.where(filters: params[:filters])

it returns no error.

Kudos to the list of the JSONB queries list here