如何在SQL中直接解析嵌套JSON对象？无需字符串预处理

原始API返回JSON #

{
  "data": {
    "transactions": {
      "edges": [
        {
          "node": {
            "text": "debet",
            "invoiceAmount": "1.0000"
          }
        },
        {
          "node": {
            "text": "kredit",
            "invoiceAmount": "-1.0000"
          }
        }
      ]
    }
  }
}

问题说明 #

此前需手动提取edges数组片段才能用OPENJSON解析，尝试直接使用路径$.data.transactions.edges.node.text时返回NULL，希望无需修改原始JSON直接完成解析。

解决方案 #

使用OPENJSON时，直接指定目标数组的完整路径$.data.transactions.edges，再在WITH子句中通过相对路径映射字段即可。同时需修正原示例中的笔误（$.node.surname应为$.node.invoiceAmount）。

完整示例代码：

DECLARE @json NVARCHAR(MAX);
SET @json = N'{
  "data": {
    "transactions": {
      "edges": [
        {
          "node": {
            "text": "debet",
            "invoiceAmount": "1.0000"
          }
        },
        {
          "node": {
            "text": "kredit",
            "invoiceAmount": "-1.0000"
          }
        }
      ]
    }
  }
}';

SELECT *
FROM OPENJSON(@json, '$.data.transactions.edges')  
WITH (
  text NVARCHAR(50) '$.node.text',
  invoiceAmount MONEY '$.node.invoiceAmount'
)

原理说明 #

• OPENJSON的第二个参数用于指定解析的JSON路径，这里直接定位到edges数组，OPENJSON会自动遍历数组中的每个元素。
• WITH子句中的路径是相对于数组单个元素的相对路径，因此$.node.text可正确获取每个元素下node对象的text字段。

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