sql - 如何从 jsonb 字段中转换文本项列表以按 uuid 类型的字段执行 IN 子句？

Question

有foo以下字段的表：

• uuid类型的 uuid
• jsonb类型的信息

除其他事项外，info字段还包含bar具有来自同一表（foo）的 uuid 列表的键。现在我需要foo为某些特定条目选择 info->'bar' 列表中 uuid 的所有条目。我尝试了下面的选择查询并得到了operator does not exist: uuid = uuid[]错误

SELECT uuid 
FROM foo 
WHERE uuid IN (SELECT jsonb_array_to_text_array(info->'bar')::uuid[] 
               FROM foo 
               WHERE uuid='some_uuid');

我需要如何修改查询才能做到这一点？我没主意了

标签： sqljsonpostgresqlpostgresql-9.6

Answer 1

没有这样的函数命名jsonb_array_to_text_array

但是您可以使用该jsonb_array_elements_text()函数，它将每个数组元素展开为一行。使用此结果，您可以按它们过滤以找到 uuid。

演示：db<>小提琴

SELECT
    elements::int AS uuid
FROM
    foo,
    jsonb_array_elements_text(info -> 'bar') elements
WHERE uuid = 4  

---

如果你想要整个记录：

演示：db<>小提琴

SELECT 
    *
FROM foo
WHERE uuid IN (
    SELECT
        elements::int
    FROM
        foo,
        jsonb_array_elements_text(info -> 'bar') elements
    WHERE uuid = 4  
)