如何按日期排序JSON列中分组后的交易记录？

现有product_history表，结构包含Product_ID、TRX_DT、TRX_AMT字段，数据如下：

使用以下SQL按Product_ID分组生成包含交易信息的JSON列：

SELECT 
    TO_JSON(
        OBJECT_CONSTRUCT_KEEP_NULL(
            'Product_ID', ANY_VALUE(t.Product_ID),
            'Transactions', ARRAY_AGG(
                    OBJECT_CONSTRUCT_KEEP_NULL(
                      'TRANSACTION_DATE', t.TRX_DT,
                      'TRANSACTION_AMOUNT', t.TRX_AMT
                    )
                     ))
            ) AS json,
    CONCAT(
        'Company_', 
        '_', t.Product_ID 
            ) AS product_code
FROM product_history AS t
GROUP BY t.Product_ID

目前已成功生成按产品分组的JSON列，但Transactions数组内的交易未按TRX_DT升序排列。尝试在语句末尾添加ORDER BY t.Product_ID, t.TRX_DT未生效，如何解决？

你之前添加的ORDER BY是对分组后的整条数据排序，而非ARRAY_AGG生成的数组内部元素排序。要让数组内的交易按日期升序排列，需要在ARRAY_AGG函数内部指定排序规则。

修改后的SQL如下：

SELECT 
    TO_JSON(
        OBJECT_CONSTRUCT_KEEP_NULL(
            'Product_ID', ANY_VALUE(t.Product_ID),
            'Transactions', ARRAY_AGG(
                    OBJECT_CONSTRUCT_KEEP_NULL(
                      'TRANSACTION_DATE', t.TRX_DT,
                      'TRANSACTION_AMOUNT', t.TRX_AMT
                    )
                    -- 在ARRAY_AGG内部添加排序规则
                    ORDER BY t.TRX_DT ASC
                     ))
            ) AS json,
    CONCAT('Company_', '_', t.Product_ID) AS product_code
FROM product_history AS t
GROUP BY t.Product_ID

关键说明 #

• ARRAY_AGG函数支持在内部通过ORDER BY子句指定聚合时的排序逻辑，生成的数组元素会按照TRX_DT升序排列。
• 外部的ORDER BY仅控制最终结果集中行的顺序，不会影响数组内部元素的顺序，因此之前的尝试无效。

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