activerecord查询整数成员的PostgresJSONB数组

fgw7neuy  于 2021-09-29  发布在  Java
关注(0)|答案(1)|浏览(548)

我有一个带有jsonb列的下表概要文件
项目类型对象更改“项目”[{“客户id”:[1,5],“其他id”:[1},{“客户id”:[4,5],“其他id”:2}],“项目”[{“客户id”:[3,6],“其他id”:3},{“客户id”:[3,5],“其他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) 有人知道我如何能够在RubyonRails中进行此查询吗。
我的rails版本是rails 4.2,ruby版本是2.4.10,我使用postgres作为我的db

zqry0prt

zqry0prt1#

我想你需要的是 jsonb_to_recordset 和横向连接。
对于以下架构和数据

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

INSERT INTO profiles2(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

相关问题