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

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

CodePudding user response：

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)