时间戳

首页 > 解决方案 > 在 RAIL 5.2 postgres12 上查询 jsonb 数组

ruby-on-rails - 在 RAIL 5.2 postgres12 上查询 jsonb 数组

问题描述

在使用 PostgreSQL 12.2 的 rails 5 上，我有这个模型

  create_table "training_opportunities", force: :cascade do |t|
    t.bigint "contact_id"
    t.string "situation"
    t.boolean "cpf"
    t.string "software_interest"
    t.string "training_type_interest"
    t.string "month"
    t.text "comment"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "status"
    t.boolean "pole_emploi", default: false
    t.jsonb "history", default: [], array: true
    t.boolean "to_be_call", default: false
    t.boolean "is_flag", default: false
    t.boolean "is_star", default: false
    t.index ["contact_id"], name: "index_training_opportunities_on_contact_id"
  end

我尝试通过历史的内容来查询 TrainingOpportunity。例如，在轨道 c

TrainingOpportunity.first.history

给

[{"date"=>"2020-07-03 09:05:00 +0000", "step"=>"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}, {"date"=>"20/07/03", "step"=>"reprise automatique de l'existant"}]

我尝试通过以下查询找到它：

TrainingOpportunity.where('history @> {"step":"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}')

但我有这个错误

ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "{")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> {"step":"I...
                                                             ^
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> {"step":"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}) LIMIT $1

我试过了 ：

TrainingOpportunity.where('history @> ?', '[{"step":"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}]')

但我收到了这个错误：

ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "[{"step":"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}]")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> '[{"step":...
                                                             ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '[{"step":"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}]') LIMIT $1

在 rmlockerd 提议之后，我尝试了以下解决方案

但没有人工作。

TrainingOpportunity.where('history @> ?', {"date"=>"20/07/03"}.to_json)
  TrainingOpportunity Load (1.0ms)  SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "{"date":"20/07/03"}")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> '{"date":"...
                                                             ^
DETAIL:  Unexpected array element.
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1



TrainingOpportunity.where('history @> ?', {'date'=>'20/07/03'}.to_json)
  TrainingOpportunity Load (1.0ms)  SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "{"date":"20/07/03"}")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> '{"date":"...
                                                             ^
DETAIL:  Unexpected array element.
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1



TrainingOpportunity.where('history @> ?', {date:'20/07/03'}.to_json)
  TrainingOpportunity Load (1.0ms)  SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "{"date":"20/07/03"}")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> '{"date":"...
                                                             ^
DETAIL:  Unexpected array element.
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1


TrainingOpportunity.where('history @> ?', [{date:'20/07/03'}].to_json)
  TrainingOpportunity Load (1.0ms)  SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '[{"date":"20/07/03"}]') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "[{"date":"20/07/03"}]")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> '[{"date":...
                                                             ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '[{"date":"20/07/03"}]') LIMIT $1

我尝试指定数组的第一个元素

TrainingOpportunity.where('history --> 0 = ?', '{"date"=>"20/07/03"}')
  TrainingOpportunity Load (0.9ms)  SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history --> 0 = '{"date"=>"20/07/03"}') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at end of input)
LINE 1: ...ies" WHERE (history --> 0 = '{"date"=>"20/07/03"}') LIMIT $1
                                                                       ^
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history --> 0 = '{"date"=>"20/07/03"}') LIMIT $1

标签： ruby-on-railspostgresql

解决方案

编辑

我的原始答案（以及您最初的尝试）假定使用 Postgresjsonb数据类型声明的列。但是，您 也将它创建为 Postgres Array（即，jsonb[]而不是jsonb）。Postgres 支持@>数组和 jsonb 类型的运算符，并且坦率地说，我没有能力帮助您解开这两者之间的某种交互。好消息是您似乎想要的东西，我认为没有必要。你有几个选择：

  1. 如果您要对这些数据进行大量查询，请考虑将其规范化为History模型并仅has_many在Training Opportunity. 除了使查询更容易之外，您还可以获得模型的所有其他优点，例如验证等。
  2. 如果您真的想将其保留为 JSON，我建议将该history列重新创建为一个jsonb类型（不带array: true. 实际上，您仍然可以将其视为一个数组（因为数组是有效的 JSON），并且该@>运算符将按照我在下面概述的方式工作。
TrainingOpportunity.where('history @> ?', [{step:"reprise automatique de l'existant"}].to_json)
 => #<ActiveRecord::Relation [#<TrainingOpportunity id: 153, history: [..., {"date"=>"20/07/03", "step"=>"reprise automatique de l'existant"}], ...>

TrainingOpportunity.where('history @> ?', [{date: "20/07/03"}].to_json)
=> #<ActiveRecord::Relation [#<TrainingOpportunity id: 153, history: [..., {"date"=>"20/07/03", "step"=>"reprise automatique de l'existant"}], ...>

推荐阅读