PostgreSQL JSONB查询报错修正：提取商品名称与数量

问题背景 #

需从rtdm_order表的integration_raw_data字段（JSONB类型）中提取商品名称及对应数量，原查询执行时触发语法错误：

ERROR:  syntax error at or near "::"
LINE 6:     FROM jsonb::->> o.integration_raw_data->>'details' AS va...
                      ^
SQL state: 42601
Character: 125

原查询语句：

SELECT o.id,
       d->>'name' AS "Unidades"
FROM rtdm_order o
CROSS JOIN LATERAL (
    SELECT value->>'name'
    FROM jsonb::->> o.integration_raw_data->>'details' AS value
) AS d
WHERE o.restaurant_id = '1'
AND created_timestamp BETWEEN '2023-09-11' AND '2023-09-23'
AND d->>'name' IS NOT NULL;

错误原因 #

原查询中jsonb::->>是无效语法，且用->>提取details会返回文本类型，无法直接用于数组遍历。要处理JSONB数组，需使用PostgreSQL内置的jsonb_array_elements函数，且需传入JSONB类型的数组（用->操作符提取，而非->>）。

修正后的查询语句 #

SELECT 
    o.id,
    value->>'name' AS product_name,
    (value->>'quantity')::INT AS product_quantity
FROM rtdm_order o
CROSS JOIN LATERAL jsonb_array_elements(o.integration_raw_data->'details') AS value
WHERE 
    o.restaurant_id = '1'
    AND o.created_timestamp BETWEEN '2023-09-11' AND '2023-09-23'
    AND value->>'name' IS NOT NULL;

关键修正点 #

• 替换错误的FROM jsonb::->> ...为jsonb_array_elements(o.integration_raw_data->'details') AS value，用该函数展开details数组的每个元素
• 使用->提取details（返回JSONB类型），确保数组函数能正确处理
• 新增提取quantity字段并转换为INT类型，满足“商品名称及对应数量”的需求
• 简化LATERAL子查询结构，直接在主SELECT中提取所需字段

内容的提问来源于stack exchange，提问作者hernan reynoso