ruby-on-rails - Rails 中的 JSONB 查询包含哈希数组的键

Question

我有一个 Rails 5 项目，它的 Page 模型有一个 JSONB 列 content。所以结构看起来像这样(减少到问题的最低限度):

#<Page id: 46, content: {..., "media" => [{ "resource_id" => 143, "other_key" => "value", ...}, ...], ...}>

我将如何编写查询来查找在 content JSONB 的 media 键下具有某个所需数字的 resource_id 的所有页面柱子？这是我所做的尝试，但没有用(我认为是因为数组的每个项目中还有其他键/值对):Page.where("content -> 'media' @> ?", {resource_id: '143'}.to_json)

编辑:这有效，但只会检查媒体数组中的第一个散列:Page.where("content -> 'media' -> 0 ->> 'resource_id' = ?", '143' )

Answer 1

使用 sql，这应该会为您提供资源 ID 为 143 的所有页面:

select * from pages p where '{"resource_id": 143}' <@ ANY ( ARRAY(select jsonb_array_elements ( content -> 'media' ) from pages where id=p.id ) );

Postgresql 有一个名为 ANY ( postgres docs ) 的函数，它使用 expression operator ANY (array) 的形式.使用给定的运算符计算左侧表达式并与数组的每个元素进行比较。

由于 ANY 的右侧参数必须是数组(而不是 json 数组)，我们使用 jsonb_array_elements 方法将 content->media json 数组转换为一组行，然后通过使用 ARRAY()。

<@运算符检查右侧的表达式是否包含左侧的表达式。例如:'{"a": 1}'::jsonb <@ '{"b": 2, "a": 1}'::jsonb将返回 true。