I have a table in the PostgreSQL 9.5 with jsonb column called segmented_data and I have a record with some data in this field
ProjectKeyword.first
=> #<ProjectKeyword:0x007fa83a17e7f8 id: 2201, project_id: 79, keyword_id: 2201, segmented_data: {"keyword_value"=>"land for sale", "dimension_value"=>{"Property type"=>"Land"}}>
How I can find this record in the database by value of one of the keyw of segmented_data? I have tried:
ProjectKeyword.where("segmented_data ->> 'keyword_value' = 'land for sale'").first
=> nil
ProjectKeyword.where('segmented_data @> ?', {keyword_value: 'land for sale'}.to_json).first
=> nil
ProjectKeyword.where('segmented_data @> ? ', '{"keyword_value":"land for sale"}' ).first
=> nil
What am I doing wrong in these queryes?
EDITED
My Model
class ProjectKeyword < ApplicationRecord
serialize :segmented_data, JSON
belongs_to :project
belongs_to :keyword
has_many :project_keyword_dimensions
has_many :dimensions, through: :project_keyword_dimensions
validates :project_id, :keyword_id, presence: true
end
Migration
class AddSegemtnedDataToProjectKeywords < ActiveRecord::Migration[5.0]
def change
add_column :project_keywords, :segmented_data, :jsonb, default: '{}'
add_index :project_keywords, :segmented_data, using: :gin
end
end
EDITED
I think I found a possible reason of this problem My db schema still was not in sql format and I added this line
config.active_record.schema_format = :sql
into application.rb file
But now if i running command
bundle exec rake db:migrate --trace
I get this error
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:migrate
** Invoke db:_dump (first_time)
** Execute db:_dump
** Invoke db:structure:dump (first_time)
** Invoke environment
** Invoke db:load_config
** Execute db:structure:dump
I, [2016-06-21T08:09:14.083751 #51538] INFO -- : [Rollbar] Scheduling item
I, [2016-06-21T08:09:14.102300 #51538] INFO -- : [Rollbar] Details: https://rollbar.com/instance/uuid?uuid=fgfffgf (only available if report was successful)
rake aborted!
TypeError: no implicit conversion of nil into String
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.rc1/lib/active_record/tasks/postgresql_database_tasks.rb:99:in `system'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.rc1/lib/active_record/tasks/postgresql_database_tasks.rb:99:in `run_cmd'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.rc1/lib/active_record/tasks/postgresql_database_tasks.rb:64:in `structure_dump'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.rc1/lib/active_record/tasks/database_tasks.rb:207:in `structure_dump'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.rc1/lib/active_record/railties/databases.rake:292:in `block (3 levels) in <top (required)>'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:248:in `block in execute'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:243:in `each'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:243:in `execute'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:187:in `block in invoke_with_call_chain'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/2.3.0/monitor.rb:214:in `mon_synchronize'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:180:in `invoke_with_call_chain'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:173:in `invoke'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.rc1/lib/active_record/railties/databases.rake:67:in `block (2 levels) in <top (required)>'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:248:in `block in execute'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:243:in `each'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:243:in `execute'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:187:in `block in invoke_with_call_chain'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/2.3.0/monitor.rb:214:in `mon_synchronize'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:180:in `invoke_with_call_chain'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:173:in `invoke'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.rc1/lib/active_record/railties/databases.rake:59:in `block (2 levels) in <top (required)>'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:248:in `block in execute'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:243:in `each'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:243:in `execute'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:187:in `block in invoke_with_call_chain'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/2.3.0/monitor.rb:214:in `mon_synchronize'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:180:in `invoke_with_call_chain'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/task.rb:173:in `invoke'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/application.rb:152:in `invoke_task'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/application.rb:108:in `block (2 levels) in top_level'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/application.rb:108:in `each'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/application.rb:108:in `block in top_level'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/application.rb:117:in `run_with_threads'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/application.rb:102:in `top_level'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/application.rb:80:in `block in run'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/application.rb:178:in `standard_exception_handling'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/lib/rake/application.rb:77:in `run'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rake-11.2.2/exe/rake:27:in `<top (required)>'
/Users/atrthur/.rbenv/versions/2.3.1/bin/rake:23:in `load'
/Users/atrthur/.rbenv/versions/2.3.1/bin/rake:23:in `<top (required)>'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/lib/bundler/cli/exec.rb:63:in `load'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/lib/bundler/cli/exec.rb:63:in `kernel_load'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/lib/bundler/cli/exec.rb:24:in `run'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/lib/bundler/cli.rb:304:in `exec'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/lib/bundler/vendor/thor/lib/thor/command.rb:27:in `run'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/lib/bundler/vendor/thor/lib/thor/invocation.rb:126:in `invoke_command'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/lib/bundler/vendor/thor/lib/thor.rb:359:in `dispatch'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/lib/bundler/vendor/thor/lib/thor/base.rb:440:in `start'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/lib/bundler/cli.rb:11:in `start'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/exe/bundle:27:in `block in <top (required)>'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/lib/bundler/friendly_errors.rb:98:in `with_friendly_errors'
/Users/atrthur/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/bundler-1.12.5/exe/bundle:19:in `<top (required)>'
/Users/atrthur/.rbenv/versions/2.3.1/bin/bundle:23:in `load'
/Users/atrthur/.rbenv/versions/2.3.1/bin/bundle:23:in `<main>'
Tasks: TOP => db:structure:dump
What is wrong with it? How to fix it?
Solution for this problem was to remove this line
serialize :segmented_data, JSON
from model ProjectKeyword
After this line was removed all queries started to work, only the problem now that the default value of : segmented_data is not the hash anymore, it became a string '{}'
So before you update data in this field I have to assign hash value to this field
project_keyword.segmented_data = {}
project_keyword.save!