Activerecord查询integer成员的postgres jsonb数组

Question

我有下面的表Profile，其中有一个jsonb列

item_type	对象更改
"项目">	[｛quot；customer_id；：[1，5]，"other_id"：1｝，｛"customer_id；；：[4，5]，"other_id"：2｝]
"项目">	[｛"customer_id；：[3，6]，"other_id"：3}，｛quot；customer_id":[3，5]，"other_id"：2}]

我认为您需要的是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