Postgres JSON 数组是否包含给定的数组元素

Question

我在stackoverflow上长时间搜索了这个答案，但没有找到有用的信息。 我在my_tbl中有如下数据:

folder_id |  links                                                                                                     
--------------+----------------------------------------------------------------
   761 | [{"ids": "[82293,82292]", "index": "index_1"}, {"ids": "[82293,82292]", "index": "index_2"}]
   769 | [{"ids": "[82323,82324]", "index": "index_3"}]
   572 | [{"ids": "[80031,79674,78971]", "index": "index_4"}]
   785 | [{"ids": "[82367,82369]", "index": "index_5"}, {"ids": "[82368,82371]", "index": "index_6"}]
   768 | [{"ids": "[82292,82306]", "index": "index_7"}]

我希望能够获取所有包含82292的links->>'ids'的行。在这种情况下，它应该返回文件夹ID 761和768。

我已经通过- jsonb_array_elements(links) ->> 'ids'将ids数组从链接中分离出来。

不确定如何进一步操作。

- stack_d_code

Answer 1

您可以使用 jsonb_array_elements 将 json 数组转换为行集。您可以使用该方法来查看 "ids" 的值。使用 @> 运算符 ，您可以检查数组是否包含一个值:

select  distinct folder_id
from    YourTable
cross join lateral
        jsonb_array_elements(links) ids(ele)      
where   (ele->>'ids')::jsonb @> '[82292]'

一个棘手的部分是"[82293,82292]"被存储为字符串，而不是数组。使用->>运算符（双>使其返回文本而不是jsonb）来检索ele->>'ids'表达式将"[82293,82292]"作为字符串检索出来。字符串的内容通过::jsonb转换为数组。

在rextester.com上的示例。

- Andomar

---

另外，我尝试从查询中获取更多的内容 - >搜索元素数组; 类似于ele.id IN（'82292'，'82324'）也可以起作用。 - stack_d_code

好像你可以使用@>来完成这个操作，检查左侧的数组是否包含右侧的数组。 - Andomar

真的很喜欢使用 cross join lateral - 很棒的解决方案。 - bobmarksie

嗨，Andomar。这个解决方案真的很有帮助。如果您正在尝试检查链接是否具有多个ID，则在Postgres 11.10中似乎可以使用以下方法：where (ele->>'ids')::jsonb @> '[82292]' AND (ele->>'ids')::jsonb @> '[82306]'然而，在我的Postgres 12.3机器上，这将导致返回0行。您知道为什么这在较新版本上可能无法正常工作吗？ - inthedark72