postgresデータベースのRails jsonbブール属性でクエリを実行するにはどうすればよいですか?
概要
Rails 7では、JSONbハッシュを設定した次のCouncilモデルがあります。 設定jsonb列でinnov_funnel_connected: trueであるすべての評議会を取得したいと考えています。
# == Schema Information
#
# Table name: councils
#
# id :uuid not null, primary key
# name :string
# settings :jsonb not null
class Council < ApplicationRecord
serialize :settings, HashSerializer
end
class HashSerializer
def self.dump(hash)
hash.to_json
end
def self.load(jsonb)
hash = JSON.load(jsonb) if jsonb.class == String
HashWithIndifferentAccess.new(hash)
end
end
irb > Council.last
=> #<Council:0x000000010bf81e00
id: "13df8a20-002c-45c1-9845-1815591688b6",
name: "Ford123",
settings: {"innov_funnel_connected"=>true}>
irb > Council.last.settings
=> {"innov_funnel_connected"=>true}
irb> Council.where("(settings ->> 'innov_funnel_connected')::boolean is true")
Council Load (0.9ms) SELECT "councils".* FROM "councils" WHERE ((settings ->> 'innov_funnel_connected')::boolean is true)
=> []
irb> Council.where("settings::jsonb @> ?", { "innov_funnel_connected" => true }.to_json)
Council Load (1.1ms) SELECT "councils".* FROM "councils" WHERE (settings::jsonb @> '{"innov_funnel_connected":true}')
=> []
irb> Council.where("settings ->> 'innov_funnel_connected' = true").first
Council Load (2.7ms) SELECT "councils".* FROM "councils" WHERE (settings ->> 'innov_funnel_connected' = true) ORDER BY "councils"."created_at" ASC, "councils"."id" ASC LIMIT $1 [["LIMIT", 1]]
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: text = boolean
LINE 1: ...ils" WHERE (settings ->> 'innov_funnel_connected' = true) OR...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
from /Users/rege/.rbenv/versions/3.0.1/lib/ruby/gems/3.0.0/gems/activerecord-7.0.3/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params'
Caused by PG::UndefinedFunction: ERROR: operator does not exist: text = boolean
LINE 1: ...ils" WHERE (settings ->> 'innov_funnel_connected' = true) OR...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
from /Users/rege/.rbenv/versions/3.0.1/lib/ruby/gems/3.0.0/gems/activerecord-7.0.3/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params'
> Council.where("(settings ->> 'innov_funnel_connected')::boolean is true)").first
Council Load (2.0ms) SELECT "councils".* FROM "councils" WHERE ((settings ->> 'innov_funnel_connected')::boolean is true)) ORDER BY "councils"."created_at" ASC, "councils"."id" ASC LIMIT $1 [["LIMIT", 1]]
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near ")"
LINE 1: ...gs ->> 'innov_funnel_connected')::boolean is true)) ORDER BY...
^
from /Users/rege/.rbenv/versions/3.0.1/lib/ruby/gems/3.0.0/gems/activerecord-7.0.3/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params'
Caused by PG::SyntaxError: ERROR: syntax error at or near ")"
LINE 1: ...gs ->> 'innov_funnel_connected')::boolean is true)) ORDER BY...
^
from /Users/rege/.rbenv/versions/3.0.1/lib/ruby/gems/3.0.0/gems/activerecord-7.0.3/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params'
解決策
エラー：
エラー: 演算子が存在しません: text = ブール値
問題を伝えています。
ここから
https://translate.google.com/translate?hl=ja&sl=en&tl=ja&u=https://www.postgresql.org/docs/current/functions-json.html
json ->> 整数 → テキスト
それで：
select '{"key": true}'::json ->> 'key' = true;
ERROR: operator does not exist: text = boolean
LINE 1: select '{"key": true}'::json ->> 'key' = true;
解決：
select ('{"key": true}'::json ->> 'key')::boolean = true;
?column?
----------
t
またはその逆:
select '{"key": true}'::json ->> 'key' = 'true';
?column?
----------
t