如何用SQL Server的OPENJSON将API返回JSON转换为指定表格？

错误原因分析 #

你遇到的错误核心是传入OPENJSON的内容不是合法JSON字符串，开头的"O"大概率是以下情况导致：

• API返回的JSON被额外前缀包裹（比如OK、Object这类非JSON字符）
• 传递给OPENJSON的不是原始JSON字符串，而是某个对象的错误字符串表示（比如直接将对象转成字符串时的异常输出）
• JSON本身存在语法错误（比如引号不匹配、多余逗号）

修复步骤与代码示例 #

1. 先验证JSON合法性 #

把从API获取的JSON内容复制到本地JSON编辑器，确认格式是否合规。如果开头有多余字符，先清理掉（比如去掉OK、var data=这类前缀）。

2. 正确使用OPENJSON + CROSS APPLY的示例 #

假设你获取的合法JSON结构如下：

{
  "items": [
    {
      "id": 1,
      "name": "Product A",
      "details": {
        "price": 100,
        "stock": 50
      }
    },
    {
      "id": 2,
      "name": "Product B",
      "details": {
        "price": 200,
        "stock": 30
      }
    }
  ]
}

目标表格结构：

对应的SQL代码：

DECLARE @json NVARCHAR(MAX) = N'
{
  "items": [
    {
      "id": 1,
      "name": "Product A",
      "details": {
        "price": 100,
        "stock": 50
      }
    },
    {
      "id": 2,
      "name": "Product B",
      "details": {
        "price": 200,
        "stock": 30
      }
    }
  ]
}';

SELECT 
  item.id,
  item.name,
  detail.price,
  detail.stock
FROM OPENJSON(@json, '$.items')
WITH (
  id INT '$.id',
  name NVARCHAR(50) '$.name',
  details NVARCHAR(MAX) '$.details' AS JSON -- 标记为嵌套JSON，供后续解析
) AS item
CROSS APPLY OPENJSON(item.details)
WITH (
  price INT '$.price',
  stock INT '$.stock'
) AS detail;

3. 处理API返回的非标准JSON #

如果API返回的JSON开头有多余字符（比如OK{"items":...}），先清理字符串：

-- 截取从第一个'{'开始的有效JSON部分
SET @json = SUBSTRING(@json, CHARINDEX('{', @json), LEN(@json));

-- 再执行上述解析逻辑

4. 常见坑点排查 #

• 确保@json变量是NVARCHAR(MAX)类型，避免编码问题导致的JSON解析失败
• 嵌套JSON必须用AS JSON标记，否则OPENJSON会将其视为普通字符串
• 路径表达式要和JSON结构严格对应（比如$.items是数组的路径，不能写错）

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