SQL Server 2019中解析JSON时divisionIds字段返回NULL的问题

你的问题出在OPENJSON的WITH子句中，divisionIds的路径指定错误。

当你使用OPENJSON(@JsonContent, '$.conversations')时，查询上下文已经切换到conversations数组中的单个对象，因此引用该对象下的divisionIds时，应该使用相对路径'$.divisionIds'，而不是带conversations的绝对路径。

另外，divisionIds是字符串数组，若想保留数组的JSON格式，建议将数据类型改为NVARCHAR(MAX)并搭配AS JSON；若需要将数组元素单独展开为多行，可结合CROSS APPLY处理。

修正后的查询（保留数组格式） #

DECLARE @JsonContent NVARCHAR(MAX);
SET @JsonContent  = '
{
  "conversations": [
    {
      "originatingDirection": "inbound",
      "conversationEnd": "2021-03-18T12:39:57.587Z",
      "conversationId": "46f7cda7-ad75-4a1e-b28c-b1c8c01e609e",
      "conversationStart": "2021-03-18T12:39:01.206Z",
      "divisionIds": [
        "aaabbbba-ertw-cldl-2021-547fff22ff33",
        "ddfrgfsc-c3e4-a8f9-b28c-b1c8c01e609e"
      ]
    }
  ]
}'

SELECT * FROM
OPENJSON(@JsonContent, '$.conversations')
WITH (
  originatingDirection NVARCHAR(50) '$.originatingDirection',
  conversationEnd NVARCHAR(50) '$.conversationEnd',
  conversationId NVARCHAR(50) '$.conversationId',
  conversationStart NVARCHAR(50) '$.conversationStart',
  divisionIds NVARCHAR(MAX) '$.divisionIds' AS JSON -- 保留数组的JSON结构
)

展开数组为多行的查询 #

如果需要将每个divisionId单独作为一行返回，可使用以下写法：

DECLARE @JsonContent NVARCHAR(MAX);
SET @JsonContent  = '
{
  "conversations": [
    {
      "originatingDirection": "inbound",
      "conversationEnd": "2021-03-18T12:39:57.587Z",
      "conversationId": "46f7cda7-ad75-4a1e-b28c-b1c8c01e609e",
      "conversationStart": "2021-03-18T12:39:01.206Z",
      "divisionIds": [
        "aaabbbba-ertw-cldl-2021-547fff22ff33",
        "ddfrgfsc-c3e4-a8f9-b28c-b1c8c01e609e"
      ]
    }
  ]
}'

SELECT 
  conv.originatingDirection,
  conv.conversationEnd,
  conv.conversationId,
  conv.conversationStart,
  div.value AS divisionId
FROM
OPENJSON(@JsonContent, '$.conversations')
WITH (
  originatingDirection NVARCHAR(50) '$.originatingDirection',
  conversationEnd NVARCHAR(50) '$.conversationEnd',
  conversationId NVARCHAR(50) '$.conversationId',
  conversationStart NVARCHAR(50) '$.conversationStart',
  divisionIds NVARCHAR(MAX) '$.divisionIds' AS JSON
) AS conv
CROSS APPLY OPENJSON(conv.divisionIds) AS div

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