PostgreSQL中如何替代->0等索引操作批量提取JSON数组值？

要批量提取attributes字段里rule->requisite数组中每个元素的link->object值，不需要逐个修改索引，用jsonb_array_elements（若字段是json类型则用json_array_elements）展开数组即可，具体SQL如下：

简洁写法 #

SELECT elem->'link'->>'object' AS object
FROM mytable,
     jsonb_array_elements(attributes->'rule'->'requisite') AS elem;

用WITH子句的写法 #

WITH expanded_rules AS (
    SELECT jsonb_array_elements(attributes->'rule'->'requisite') AS elem
    FROM mytable
)
SELECT elem->'link'->>'object' AS object
FROM expanded_rules;

说明 #

• jsonb_array_elements(attributes->'rule'->'requisite')会把JSON数组的每个元素拆分成单独的行
• ->>操作符用于提取JSON字段的文本值，如果需要保留JSON类型，改用->即可
• 语句中的, 等价于CROSS JOIN，会将原表每行与展开后的数组元素进行关联

执行后就能得到期望的结果：

object
------
a
b
c

内容的提问来源于stack exchange，提问作者Marc Le Bihan