PostgreSQL中从JSON列提取数量值并计算自定义列总和

要实现tickets表中新增自定义列计算总费用的需求，可以通过PostgreSQL的JSON函数和字符串处理函数完成，具体SQL语句如下：

SELECT
    t.*,
    SUM(
        -- 提取数量并转换为数值，乘以单价3
        (SPLIT_PART(item, '::', 3)::TEXT::NUMERIC) * 3
    ) AS total_cost
FROM tickets t
-- 将JSON数组展开为多行数据
CROSS JOIN LATERAL json_array_elements(t.printer_catalog::JSON) AS items(item)
GROUP BY t.id; -- 替换为tickets表实际的主键或唯一标识列

关键步骤说明： #

• json_array_elements(t.printer_catalog::JSON)：将printer_catalog列的JSON数组展开为单独的行，每个数组元素对应一行数据。
• SPLIT_PART(item, '::', 3)：将每个字符串按::分割，取第三部分（即数量部分，如"2 pcs"）。
• (SPLIT_PART(...)::TEXT::NUMERIC)：将提取的数量字符串转换为数值类型，自动忽略"pcs"文本。
• SUM(...) AS total_cost：对每个服务的费用（数量×3）求和，得到总费用作为自定义列。

注意：若printer_catalog存储格式并非标准JSON数组，需先确认数据结构。如果是JSON对象，需调整为对应处理函数（如json_object_keys）适配格式。

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