SQL Server中如何从JSON字段获取支付的最早/最晚日期？

原查询返回NULL的核心原因是：$.bank_orders和$.itens都是数组结构，而JSON_VALUE只能定位到单个JSON值，无法直接遍历数组路径，因此无法提取到目标数据。

要提取嵌套数组中的支付日期并计算最大、最小值，需要用OPENJSON逐层展开嵌套的数组结构，再进行聚合计算，具体实现如下：

方案1：按单条记录计算最大/最小支付日期 #

如果需要为bank_payments表中的每条记录，单独计算其JSON数据里的最早和最晚支付日期：

SELECT
    bp.id, -- 假设表有主键id，用于标识每条记录
    MAX(CAST(j2.payment_date AS DATETIME)) AS max_payment_date,
    MIN(CAST(j2.payment_date AS DATETIME)) AS min_payment_date
FROM bank_payments bp
-- 展开bank_orders数组，得到每个订单对象
CROSS APPLY OPENJSON(bp.json_data, '$.bank_orders') AS j1
-- 展开每个订单下的itens数组，提取payment_date字段
CROSS APPLY OPENJSON(j1.value, '$.itens')
WITH (
    payment_date VARCHAR(30) '$.payment_date'
) AS j2
GROUP BY bp.id

方案2：计算全局所有记录的最大/最小支付日期 #

如果需要计算表中所有JSON数据里的最早和最晚支付日期：

SELECT
    MAX(CAST(j2.payment_date AS DATETIME)) AS global_max_payment_date,
    MIN(CAST(j2.payment_date AS DATETIME)) AS global_min_payment_date
FROM bank_payments bp
CROSS APPLY OPENJSON(bp.json_data, '$.bank_orders') AS j1
CROSS APPLY OPENJSON(j1.value, '$.itens')
WITH (
    payment_date VARCHAR(30) '$.payment_date'
) AS j2

关键细节说明 #

1. CROSS APPLY与OUTER APPLY的区别：如果部分记录的JSON数据中没有bank_orders或itens数组，使用CROSS APPLY会过滤掉这些记录；若要保留这些记录（此时对应聚合值为NULL），可以替换为OUTER APPLY。
2. 日期类型转换：JSON中的payment_date是字符串格式，必须通过CAST转换为DATETIME类型，才能正确进行日期比较和聚合计算。

内容的提问来源于stack exchange，提问作者Ângelo Rigo