json - 如何将 PostgreSQL jsonb_array_elements 与 WHERE IN 子句一起使用

Question

我有occasion 和relation 表。 relation.occasions是一个json类型的字段，里面包含了一个occasion key的数组。我想通过关系获取 occasion 记录，其中 occasion.key 在 relation.occasions 中。假设这是我的数据库数据:

场合

        key      |     name      |
-------------------------------------
    BIRTHDAY     |   Birthday    |
    ANNIVERSARY  |   Anniversary |

关系

    key          | occasions                         |
------------------------------------------------------
    FATHER       |   [ "BIRTHDAY", "ANNIVERSARY" ]   |
    FRIEND       |   [ "BIRTHDAY" ]                  |

这是我尝试使用的查询:

SELECT * FROM occasion o WHERE o.key IN
(SELECT json_array_elements(r.occasions)::text from relation r WHERE r.key = 'FATHER')

结果是 0 行而不是预期的 2 行。有人可以提示我我做错了什么吗？我还能如何达到预期的结果？

Answer 1

函数 json_array_elements() 返回一个集合，而不是数组，因此您应该将它用作行源，而不是在选择列表中。

正确的查询应该是:

SELECT o.*
FROM occasion o
JOIN (
    SELECT DISTINCT j.value
    FROM relation
    JOIN LATERAL json_array_elements_text(occasions) j(value) ON true
    WHERE key = 'FATHER') sub ON o.key = sub.value;

你也可以在没有子查询的情况下将其展平，但我发现这个版本更具可读性(查询计划器无论如何都会进行展平)。