My table in SQL Server with an nvarchar(max) column holds multiple JSON arrays in each cell. I am trying to parse the 'result' items into separate rows.
JSON array:
[
{"successful":true,
"fields":
[{"index":0, "publicPath": "date"},
{"index":1, "publicPath": "accountID"},
{"index":2, "publicPath": "amount"},
{"index":3, "publicPath": "description"}],
"result":
[{"0":2025-07-25",
"1": "1234",
"2": "100.00",
"3": "Some description here"},
{"0":2025-04-22",
"1": "4567",
"2": "12540.00",
"3": "Some description here"},
{"0":2025-02-22",
"1": "1234",
"2": "700.00",
"3": "Some description here"}]},
{"successful":true,
"fields":
[{"index":0, "publicPath": "date"},
{"index":1, "publicPath": "accountID"},
{"index":2, "publicPath": "amount"},
{"index":3, "publicPath": "description"}],
"result":
[{"0":3025-07-25",
"1": "2222",
"2": "2222.00",
"3": "Some description here"},
{"0":1025-04-22",
"1": "4567",
"2": "88540.00",
"3": "Some description here"},
{"0":2025-02-22",
"1": "1234",
"2": "700.00",
"3": "Some description here"}]},
{"successful":true,
"fields":
[{"index":0, "publicPath": "date"},
{"index":1, "publicPath": "accountID"},
{"index":2, "publicPath": "amount"},
{"index":3, "publicPath": "description"}],
"result":
[{"0":2025-07-25",
"1": "1234",
"2": "100.00",
"3": "Some description here"},
{"0":2025-04-22",
"1": "4567",
"2": "12540.00",
"3": "Some description here"},
{"0":2025-02-22",
"1": "1234",
"2": "876.00",
"3": "Some description here"}
]
}
]
I only care about the result array within the JSON string. Multiple JSON strings are contained in individual cells within table JSONTable in column Data. I've had limited success with:
SELECT
JSON_Value(x.value, '$.result[*]."0"') AS 'date',
JSON_Value(x.value, '$.result[*]."1"') AS 'accountID',
JSON_Value(x.value, '$.result[*]."2"') AS 'amount',
JSON_Value(x.value, '$.result[*]."3"') AS 'description'
FROM
[JSONTable] js
CROSS APPLY
OPENJSON([Data]) AS X
It gets only the last-most result from the JSON table, returning NULL for all other calls and does not segment individual results within each JSON segment into their own rows:
| date | accountID | amount | description |
|---|---|---|---|
| NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL |
| 2025-02-22 | 1234 | 876.00 | Some description here |
It should parse individual results within each of the concatenated calls as well as all of the 'result' fields within each call and split them into their own rows (I am constrained by the JSON structure):
| date | accountID | amount | description |
|---|---|---|---|
| 2025-07-25 | 1234 | 100.00 | Some description here |
| 2025-04-22 | 4567 | 12540.00 | Some description here |
| 2025-02-22 | 1234 | 700.00 | Some description here |
| 3025-07-25 | 2222 | 2222.00 | Some description here |
| 1025-04-22 | 4567 | 88540.00 | Some description here |
| 2025-02-22 | 1234 | 700.00 | Some description here |
| 2025-07-25 | 1234 | 100.00 | Some description here |
| 2025-04-22 | 4567 | 12540.00 | Some description here |
| 2025-02-22 | 1234 | 876.00 | Some description here |
$.result[*] is not a proper JSON path. SQL Server (before 2025) doesn't support wildcards, and if it did it would have no way of returning multiple results in JSON_VALUE anyway.
You have nested arrays. So you need to feed one OPENJSON into the next using CROSS APPLY.
SELECT
j2.*
FROM
JSONTable jt
CROSS APPLY
OPENJSON(jt.Data)
WITH (result nvarchar(max) AS JSON) j1
CROSS APPLY
OPENJSON(j1.result)
WITH (
[date] date '$."0"',
accountID int '$."1"',
amount decimal(19,2) '$."2"',
description nvarchar(100) '$."3"'
) j2;