Snowflake中SELECT查询结果转JSON失败，求解决方案

期望输出格式：

[
    {
        "COL1_VALUE": "TEST_TEST1",
        "COL2_VALUE": "COL2_TEST1",
        "COL3_VALUE": "11/17/2022",
        "COL4_VALUE": "ABCD",
        "COL5_VALUE": null,
        "COL6_VALUE": null,
        "COL7_VALUE": "123456",
        "COL8_VALUE": 678
    },
    {
        "COL1_VALUE": "TEST_TEST1",
        "COL2_VALUE": "COL2_TEST2",
        "COL3_VALUE": "11/8/2022",
        "COL4_VALUE": "XYD",
        "COL5_VALUE": null,
        "COL6_VALUE": null,
        "COL7_VALUE": "123456",
        "COL8_VALUE": 678
    },
    {
        "COL1_VALUE": "TEST_TEST2",
        "COL2_VALUE": "COL2_TEST6",
        "COL3_VALUE": "11/15/2022",
        "COL4_VALUE": "RTY",
        "COL5_VALUE": "RYU",
        "COL6_VALUE": "GHJ",
        "COL7_VALUE": "67856",
        "COL8_VALUE": 123
    }
]

当前使用的SQL语句：

SELECT TO_JSON(OBJECT_CONSTRUCT('Values', ARRAY_AGG(OBJECT_CONSTRUCT('COL1_VALUE', COL1_VALUE ,'COL2_VALUE', COL2_VALUE, 'COL3_VALUE', COL3_VALUE, 'COL4_VALUE', COL4_VALUE, 'COL5_VALUE', COL5_VALUE, 'COL6_VALUE', COL6_VALUE, 'COL7_VALUE', COL7_VALUE, 'COL8_VALUE', COL8_VALUE)))) FROM TABLENAME

无法得到预期输出，以下是解决建议：

• 移除外层对象包装：你的SQL用OBJECT_CONSTRUCT('Values', ...)给数组套了一层{"Values": [...]}的结构，但预期输出是纯JSON数组。直接对ARRAY_AGG的结果使用TO_JSON即可：

  SELECT TO_JSON(ARRAY_AGG(OBJECT_CONSTRUCT(
      'COL1_VALUE', COL1_VALUE,
      'COL2_VALUE', COL2_VALUE,
      'COL3_VALUE', COL3_VALUE,
      'COL4_VALUE', COL4_VALUE,
      'COL5_VALUE', COL5_VALUE,
      'COL6_VALUE', COL6_VALUE,
      'COL7_VALUE', COL7_VALUE,
      'COL8_VALUE', COL8_VALUE
  ))) FROM TABLENAME
  

• 强制保留NULL字段：默认的OBJECT_CONSTRUCT会自动忽略值为NULL的键值对，导致预期中的"COL5_VALUE": null这类字段缺失。以Snowflake为例，改用OBJECT_CONSTRUCT_KEEP_NULL函数，它会保留值为NULL的键：

  SELECT TO_JSON(ARRAY_AGG(OBJECT_CONSTRUCT_KEEP_NULL(
      'COL1_VALUE', COL1_VALUE,
      'COL2_VALUE', COL2_VALUE,
      'COL3_VALUE', COL3_VALUE,
      'COL4_VALUE', COL4_VALUE,
      'COL5_VALUE', COL5_VALUE,
      'COL6_VALUE', COL6_VALUE,
      'COL7_VALUE', COL7_VALUE,
      'COL8_VALUE', COL8_VALUE
  ))) FROM TABLENAME
  

• 确保数据类型匹配：OBJECT_CONSTRUCT_KEEP_NULL会保留字段原始数据类型，比如COL8_VALUE的数值类型会保持为数字而非字符串，无需额外处理。

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