json - 如何在WHERE <值> IN子句中使用PostgreSQL json_array_elements

我有occasion和relation表格。relation.occasions是一个json类型字段，它包含一个场合键数组。我想按关系获取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行。有人能告诉我我做错了什么吗？不然我怎么才能达到预期的效果呢？

最佳答案

函数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;

您也可以在不使用子查询的情况下将其展平，但我发现此版本更具可读性（无论如何，查询计划器将进行展平）。

关于json - 如何在WHERE <值> IN子句中使用PostgreSQL json_array_elements，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38159694/