get Json values and Json string from Json Array in SQL Server

Question

I have a JSON string like this:

{
    "success": "true",
    "data": [
              { "ID": 1, "name": "abc" },
              { "ID": 2, "name": "def" }
            ]
}

I need output like below:

ID	name	JsonString
1	abc	{"ID": 1, "name": "abc"}
2	def	{"ID": 2, "name": "def"}

I need the query in SQL Server

Answer 1

The only interesting things here are the initial path to OPENJSON (since we only care about data) and the property JsonString reflecting the original JSON, which needs a path and an override.

DECLARE @json NVARCHAR(MAX) = '{"success":"true","data":[{"ID":1,"name":"abc"},{"ID":2,"name":"def"}]}';

SELECT * 
FROM OPENJSON(@json, '$.data')
WITH (ID INT, name VARCHAR(100), JsonString NVARCHAR(MAX) '$' AS JSON)