How to extract array data from a JSON Column in SQL Server (OPENJSON, JSON_QUERY)

Question

We have a SQL Server table that has a varchar(max) column with JSON data, but one of the fields is an array. Try as I might I can't get it to extract these one-per-row. In the past I've managed to do it where each array was made up of named members, so using another CROSS APPLY WITH (...) and naming the members. But struggling to work out how to do it with a more simple list of values.

Pseudo Code:

DROP TABLE IF EXISTS #MyTable
CREATE TABLE #MyTable (
  Id uniqueidentifier
  ,MyJsonText nvarchar(max))
INSERT INTO #MyTable(Id, MyJsonText) VALUES
('EDA4A604-59F4-4E4E-9C20-08D82314D8F6', '{"InitialCost":75.0,"OtherCost":50.0,"DatesOfVisit":["Wed, 26 Feb 2020 00:00:00 GMT","Fri, 20 Mar 2020 00:00:00 GMT"],"CatNumber":"PH123456"}')

SELECT ISJSON(MyJsonText) FROM #MyTable -- Check JSON ok

SELECT
  mt.Id
  ,mt_j.InitialCost
  ,mt_j.OtherCost
  ,mt_j.CatNumber
FROM #MyTable mt 
CROSS APPLY OPENJSON(mt.MyJsonText) WITH (
  InitialCost decimal(8,2)
  ,OtherCost decimal(8,2)
  ,CatNumber varchar(50)) as mt_j

The above works with the "top level" data from the JSON, but how can I return this:

Id                                      InitialCost       OtherCost  DatesOfVisit              CatNumber
EDA4A604-59F4-4E4E-9C20-08D82314D8F6    75.00             50.00      Wed, 26 Feb 2020 00:00:00 GMT PH123456
EDA4A604-59F4-4E4E-9C20-08D82314D8F6    75.00             50.00      Fri, 20 Mar 2020 00:00:00 GMT PH123456

Much appreciate any guidance!

Answer 1

Nest your JSON calls:

SELECT MT.Id,
       JT.InitialCost,
       JT.OtherCost,
       DV.value AS DateOfVisit
FROM #MyTable MT
     CROSS APPLY OPENJSON(MT.MyJsonText)
                 WITH (InitialCost decimal(8,2),
                       OtherCost decimal(8,2),
                       DatesOfVisit nvarchar(MAX) AS JSON) JT
     CROSS APPLY OPENJSON(DatesOfVisit) DV;