Postgres中Json(b) int数组包含任何一个数组值

Question

TLDR：如何查找包含给定数组/列表中一个数字的json数字数组的每个记录。

好的，我尝试了很多次，阅读了很多其他线程，但我还没有找到有效的解决方案。我在Postgres中有一些JSON对象需要查找。它们包含多选选择的索引。

对象：

[{"id": 5, "list": [1, 2, 3]}, {"id": 6, "list": [4, 5, 6]}]

我需要选择所有包含项目1或5的对象。目前，我只能检查一个值。

SELECT '[1, 2, 3, 4]'::jsonb @> '3'::jsonb as result;

或者如果两者都存在：

SELECT '[1,2,3,4]'::jsonb @> '[1, 2]'::jsonb as result;

或者，有趣的是，如果我有一个字符串数组，我可以做我需要做的事情：

SELECT '["1","2","3","4"]'::jsonb ?| array['1', '5'] as result;

如何将其应用于JSON数字数组？我尝试了int []的类型转换和许多其他方法，没有起作用。我要么需要将数字数组转换为文本数组以便处理它，要么找到正确的类型转换来处理右边。这也不起作用：? | array [1，5] :: text [] 解决方案1：

好吧，首先想到的是使用OR，看起来仍然是最快和最简单的解决方案。

解决方案2：

我终于找到了一种更短的方法（就像我认为它应该工作的方式），但它是一个类型转换地狱（9.4+），而且看起来不太好看： SELECT array_to_json(translate('[1,2,3,4]'::jsonb::text, '[]', '{}')::text[])::jsonb ?| array['1','5'] as result; 基本上将整数转换为字符串。

- gerritg

---

当然不能使用 OR 选项？.. SELECT '[1, 2, 3, 4]'::jsonb @> '3'::jsonb OR '[1, 2, 3, 4]'::jsonb @> '5'::jsonb as result - Vao Tsun

“OR” 是我想到的第一个解决方案，但是如果选择的选项很多，它肯定会使查询变得非常复杂。当然，这只是一个非常复杂查询的一部分 :) 但是，目前看来，这似乎是最简洁和最快速的解决方案。 - gerritg

Answer 1

您可以使用OR：

t=# SELECT '[1, 2, 3, 4]'::jsonb @> '3'::jsonb OR '[1, 2, 3, 4]'::jsonb @> '5'::jsonb as result;
 result
--------
 t
(1 row)

或将未嵌套的jsonb数组聚合成整数数组以比较它们是否相交：

t=# with c(j) as (values('[1, 2, 3, 4]'::jsonb))
, a as (select j,jsonb_array_elements(j) ja from c)
select array_agg(ja), j, array_agg(ja::text::int) && array[5,4] from a group by j;
 array_agg |      j       | ?column?
-----------+--------------+----------
 {1,2,3,4} | [1, 2, 3, 4] | t
(1 row)

因为在您的示例中，所有的jsonb运算符都按照文档所述的方式进行操作。我没有看到更优雅且更简短的解决方案...

- Vao Tsun

---

是的，“OR”解决方案是我首先想到的，但我认为可能会有更简短/优雅的方法。不过，考虑到它仍然是最快和最简单的解决方案，所以我接受了它。暂时就这样吧 :) - gerritg

Answer 2

你也可以这样做：

select translate('[1,2,3]', '[]','{}')::int[] && ARRAY[1,3]

- ardmont

Answer 3

你可以使用 jsonb_array_elements，转换为文本，然后转换为整数，并与 array_agg 结合使用。

WITH j AS (SELECT array_agg(h), othercol, thirdcol a FROM
  (SELECT jsonb_array_elements('[53,22,41,23]')::text::int h, othercol,
    thirdcol) AS j GROUP BY othercol, thirdcol)
SELECT othercol, thirdcol FROM j WHERE j.a && ARRAY[44,13,12,41];

如果考虑到 group by 语句，这并不是特别优雅。

- coladict

---

这确实是我没有想过的，但正如你所说：group by并不理想。但还是谢谢！ - gerritg