arrays - 如何遍历包含在 JSONb 字段中的数组？

Question

在 Postgres 9.6 中，如何循环遍历 PLPGSQL 函数内 jsonb 字段中包含的数组？

这是我的字段声明:

CREATE TABLE afact_rule(
    ...
    expressions         jsonb,
    ...
)

这是我的功能:

        FOR expression IN SELECT * FROM json_array_elements(rule.expressions) LOOP
            CASE expression.field 
                WHEN 'task_id' THEN
                    ... whatever ...
            END CASE;
        END LOOP;   

这是我在使用它时遇到的错误:

LINE 1: SELECT * FROM json_array_elements(rule.expressions)
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT * FROM json_array_elements(rule.expressions)

遍历包含在 jsonb 字段中的数组的最佳(和有效)方法是什么？

rule.expressions 的内容是:

 [{"field": "dep_id", "value": 1, "operator_code": 1},
  {"field": "title", "value": "customer", "operator_code": 2}]

Answer 1

赞@a_horse commented , 使用 jsonb_array_elements()取消嵌套 jsonb 数组。此外，我建议使用 [INNER] JOIN 而不是 CROSS JOIN。 CROSS JOIN 是 - 的冗长变体语法变体，并且比 - 一个简单的逗号 (,) 绑定(bind)得更紧密:

SELECT * -- do something?
FROM   afact_rule a
JOIN   LATERAL jsonb_array_elements(a.expressions) exp ON exp->>'field' = 'task_id';

立即消除 JOIN 条件中不合格的元素(和行)应该是最有效的。 (语法非常清晰，即使它产生与后面的 WHERE 条件相同的查询计划。)

在这种情况下，LATERAL 关键字是可选噪声，因为它无论如何都假定用于 FROM 子句中的集合返回函数 (SRF)。

exp 是表和 列自动为 SRF 函数结果的别名。

相关:

• How to turn json array into postgres array?