时间戳

首页 > 解决方案 > Activerecord查询整数成员的postgres jsonb数组

ruby-on-rails - Activerecord查询整数成员的postgres jsonb数组

问题描述

我有下表配置文件与 jsonb 列

物品种类 object_changes
“物品” [{"customer_id": [1, 5], "other_id": 1}, {"customer_id": [4, 5], "other_id": 2}]
“物品” [{"customer_id": [3, 6], "other_id": 3}, {"customer_id": [3, 5], "other_id": 2}]

我希望能够使用活动记录查询以查找所有具有 customer_id 5 的行。

我尝试执行以下操作，但它不起作用

Profile.where("object_changes->'customer_id' @> '5'")

Profile.where("object_changes->'customer_id' @> ?::jsonb", [5].to_json)

Profile.where("? = ANY (object_changes->>'customer_id')", 5)

有谁知道我如何能够在 Ruby on Rails 中进行此查询。

我的 Rails 版本是 Rails 4.2，Ruby 版本是 2.4.10，我使用 postgres 作为我的数据库

标签： ruby-on-railsrubypostgresqlruby-on-rails-4activerecord

解决方案

我认为您需要的是jsonb_to_recordset横向连接的组合。

对于以下架构和数据

CREATE TABLE profiles (
  id integer,
  item_type text,
  object_changes jsonb
);

INSERT INTO profiles(id, item_type, object_changes) VALUES
  (1, 'Item', '[{"customer_id": [1, 5], "other_id": 1}, {"customer_id": [4, 5], "other_id": 2}]'::jsonb),
  (2, 'Item', '[{"customer_id": [3, 6], "other_id": 3}, {"customer_id": [3, 5], "other_id": 2}]'::jsonb),
  (3, 'Item', '[{"customer_id": [4, 7], "other_id": 3}, {"customer_id": [8, 9], "other_id": 2}]'::jsonb);

像这样的东西会起作用：

SELECT distinct profiles.*
FROM 
  profiles, 
  jsonb_to_recordset(profiles.object_changes) AS changes(customer_id integer[], other_id integer)
WHERE 5 = ANY(changes.customer_id);


 id | item_type |                                  object_changes
----+-----------+----------------------------------------------------------------------------------
  2 | Item      | [{"other_id": 3, "customer_id": [3, 6]}, {"other_id": 2, "customer_id": [3, 5]}]
  1 | Item      | [{"other_id": 1, "customer_id": [1, 5]}, {"other_id": 2, "customer_id": [4, 5]}]
(2 rows)

所以使用 AR 查询接口的最终解决方案类似于（我硬编码要查找的值，但我相信你明白了，参数化不是问题）：

Profile.find_by_sql(<<~SQL)
  SELECT distinct profiles.*
  FROM 
    profiles, 
    jsonb_to_recordset(profiles.object_changes) AS changes(customer_id integer[], other_id integer)
  WHERE 5 = ANY(changes.customer_id)
SQL

推荐阅读