SQL Server 从 JSON 数组获取最早和最晚付款日期

2026-10-24支付小周

结论

bank_orders 和 itens 都是 JSON 数组，需要先用 OPENJSON 展开，再进行聚合。把 payment_date 转换为 datetime2(3) 后，可以用 MIN 和 MAX 分别取得最早和最晚的付款日期。

SELECT
    bp.id,
    d.first_payment_date,
    d.last_payment_date
FROM bank_payments AS bp
OUTER APPLY
(
    SELECT
        MIN(TRY_CONVERT(datetime2(3), JSON_VALUE(i.value, '$.payment_date')))
            AS first_payment_date,
        MAX(TRY_CONVERT(datetime2(3), JSON_VALUE(i.value, '$.payment_date')))
            AS last_payment_date
    FROM OPENJSON(bp.json_data, '$.bank_orders') AS bo
    CROSS APPLY OPENJSON(bo.value, '$.itens') AS i
) AS d;

请将 bp.id 替换为 bank_payments 表中实际使用的主键或业务标识列。

为什么原查询返回 NULL

原查询使用了以下路径：

$.bank_orders.itens.payment_date

但 bank_orders 和 itens 都是数组。JSON_VALUE 用于读取指定路径上的单个标量值，不会自动遍历数组，所以无法通过这条路径直接取得 payment_date。

如果只想读取第一个 bank_orders 中的第一条付款记录，可以明确指定数组下标：

SELECT
    TRY_CONVERT(
        datetime2(3),
        JSON_VALUE(json_data, '$.bank_orders[0].itens[0].payment_date')
    ) AS first_array_payment_date
FROM bank_payments;

这个查询取得的是数组中的第一项，不一定是时间最早的一项。

获取最早和最晚付款日期

如果一条 json_data 可能包含多个 bank_orders，而每个订单又可能包含多条 itens，可以依次展开这两层数组：

SELECT
    bp.id,
    MIN(p.payment_date) AS first_payment_date,
    MAX(p.payment_date) AS last_payment_date
FROM bank_payments AS bp
CROSS APPLY OPENJSON(bp.json_data, '$.bank_orders') AS bo
CROSS APPLY OPENJSON(bo.value, '$.itens')
WITH
(
    payment_date datetime2(3) '$.payment_date'
) AS p
GROUP BY bp.id;

对表中的每条记录，这个查询会返回：

first_payment_date：时间最早的付款日期；
last_payment_date：时间最晚的付款日期。

CROSS APPLY 会过滤掉没有 bank_orders 或没有 itens 的表记录。如果需要保留这些记录，并让日期显示为 NULL，可以使用前面的 OUTER APPLY 聚合写法。

“第一个日期”的两种含义

“第一个日期”可能有两种含义：

时间最早的日期，应使用 MIN(payment_date)。
JSON 数组中的第一项，应使用 [0]，例如：

JSON_VALUE(json_data, '$.bank_orders[0].itens[0].payment_date')

只有在生成数据时明确规定了 JSON 数组顺序的业务含义，才能把第一项当作第一笔付款。如果要查询最早的付款时间，应使用 MIN，不要依赖数组的排列顺序。

注意事项

比较日期前，应先把字符串转换为真正的日期类型：

TRY_CONVERT(datetime2(3), payment_date)

不要直接对日期字符串使用 MIN 或 MAX。示例中的日期格式按字典顺序比较通常也能得到正确结果，但数据格式一旦不统一，就可能出现错误。

遇到无效日期时，TRY_CONVERT 会返回 NULL，聚合函数则会忽略这些值。如果需要检查脏数据，可以单独查询转换失败的记录：

SELECT
    bp.id,
    JSON_VALUE(i.value, '$.payment_date') AS invalid_payment_date
FROM bank_payments AS bp
CROSS APPLY OPENJSON(bp.json_data, '$.bank_orders') AS bo
CROSS APPLY OPENJSON(bo.value, '$.itens') AS i
WHERE JSON_VALUE(i.value, '$.payment_date') IS NOT NULL
  AND TRY_CONVERT(
        datetime2(3),
        JSON_VALUE(i.value, '$.payment_date')
      ) IS NULL;

这些查询都要求 json_data 中保存的是完整且有效的 JSON。问题里的片段省略了数组和对象的结束符，实际存储的值必须包含完整的 ] 和 }。