Rails jsonb query returns empty

Question

I am trying to query a datatype in my jsonb column. When I query with standard SQL, the response is empty. I was using this as a reference.

My data: id: "f0242deb-fef5-4221-b4eb-68610d355a06", metadata: {"classification"=>"unclassified"}

My controller query: Transaction.where('metadata @> ?', {classification: 'unclassified'}.to_json)

I get this as a response Transaction Load (39.0ms) SELECT "transactions".* FROM "transactions" WHERE (metadata @> '{"classification":"unclassified"}') => []

I would expect the response to include the above dataset since the classification matches. Where am I going wrong?

Schema and models for reference.

schema.rb
create_table "transactions", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.jsonb "metadata", default: "{}", null: false
  t.index ["metadata"], name: "index_transactions_on_metadata", using: :gin
end

transaction.rb
class Transaction < ApplicationRecord
  require 'json'
  include ActiveModel::Serialization

  serialize :metadata
  store_accessor :metadata, :classification, :amount, :merchant, :category

  validates :transaction_date, :classification, :amount, :merchant, :category, presence: true

end

user_12219679 · Answer

i think the problem is json serialization, it looks like this will force convert the input json data, take a look (on my project):

> Event.create(payload: {question: "jsonb index", answer: "json serialize"})
> ... ["payload", "\"---\n:question: jsonb index\n:answer: json serialize\n\""]

in database, that payload will be saved as: "--- :question: jsonb index :answer: json serialize ", and the jsonb query can not find it.

Now change the serializer

#require 'json'
#include ActiveModel::Serialization
serialize :payload, HashSerializer
store_accessor :payload, :question, :answer

the payload will be saved as {"answer": "serialize", "question": "jsonb index"} and jsonb query can find it.

Of course it also works if i remove serialize

Keep in mind that database adapters handle certain serialization tasks for you. For instance: json and jsonb types in PostgreSQL will be converted between JSON object/array syntax and Ruby Hash or Array objects transparently. There is no need to use #serialize in this case.

Rails jsonb query returns empty

1 Answers