Parse multiple JSON items from a table cell

Question

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

Your JSON isn't properly formatted, missing quote before the dates. — Charlieface
– Charlieface, Commented Jul 24 at 17:24

Charlieface · Accepted Answer · 2025-07-29 13:25:15Z

6

$.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;

db<>fiddle

edited Jul 29 at 13:25

answered Jul 24 at 17:22

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wqw · Accepted Answer · 2025-07-29 14:20:12Z

0

Here is the same approach

SELECT      j3.*
FROM        JSONTable jt
CROSS APPLY OPENJSON(jt.Data) j1
CROSS APPLY OPENJSON(j1.value, '$.result') j2
CROSS APPLY OPENJSON(j2.value) WITH (
                [0] DATE,
                [1] INT,
                [2] DECIMAL(19,2),
                [3] NVARCHAR(100)
            ) j3
ORDER BY    j1."key", j2."key"

answered Jul 29 at 14:20

wqw

12k2 gold badges37 silver badges43 bronze badges

Collectives™ on Stack Overflow

Parse multiple JSON items from a table cell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related