时间戳

首页 > 解决方案 > 如何选择jsonb与整数和文本数组重叠的行？(PostgreSQL)

postgresql - 如何选择jsonb与整数和文本数组重叠的行？(PostgreSQL)

问题描述

标题可能听起来不太清楚，所以这里有一个示例场景。

这是一张名为test.

 id |         json (this is "jsonb")         
----+----------------------
  1 | [0]
  2 | [1, 2, 3]
  3 | [4]
  4 | ["4"]
  5 | ["5", "6"]
  6 | ["5", "6", "7", "8"]

现在，您必须选择json在 array 中至少包含一个元素的行。

如果你跑select * from test where json::jsonb ?| array['4', '8', '10'];...

 id |         json         
----+----------------------
  4 | ["4"]
  6 | ["5", "6", "7", "8"]

可以看到，ID 3 的行被遗漏了。这是因为jsonID 3 是一个整数数组。

如果将上面的语句改写成select * from test where json::jsonb ?| array[4, '8', '10'];...

ERROR:  operator does not exist: jsonb ?| integer[]
LINE 1: select * from test where json::jsonb ?| array[4, '8', '10'];
                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

该?|运算符似乎与整数不兼容。

是否可以选择jsonb（由整数和文本混合而成）与数组重叠的行？我从doc中找不到任何功能来实现这一点。

我不是数据库专家，所以任何想法都会非常感激！

标签： postgresql

解决方案

您可以EXISTS在数组元素上使用带有子查询的子句：

SELECT *
FROM demo
WHERE EXISTS(
  SELECT 1
  FROM jsonb_array_elements(data) AS val
  WHERE val IN ('4', '8', '10', '"4"', '"8"', '"10"')
)

（在线演示）

请注意这里的IN比较jsonb值，如果您不想同时指定整数和字符串，您也可以将值转换为您想要的任何值并进行比较。

推荐阅读