Unable to parse OpenJson if value dont have key name

Question

I have some json that I would like to parse in SQL Server 2019. There is a hierarchy structure of Conversations->Divisions. I would like to write a query that parses the whole hierarchy

Divisons are coming as null

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(50) '$.conversations.divisionIds'

)

Answer 1

You need a different path ('$.divisionIds') and AS JSON to parse the $.divisionIds part of the input JSON and an additional APPLY operator to parse the nested JSON array:

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
) c
OUTER APPLY OPENJSON (c.divisionIds) WITH (
   divisionId NVARCHAR(50) '$'
) d