TSQL, how to retrieve data from multiple JSON rows from a database

user_8457294 0
Viewed 147

I have in my table rows with JSON same as a example:

json_data
[{"d":"2021-05-05T12:16:18.9175335","l":"temp12@cor.net","a":"test1","c":"Kom1"}]
[{"d":"2021-05-05T12:16:37.7258608","l":"temp12@cor.net","a":"test2","c":"Kom2"}]
[{"d":"2021-05-05T12:17:30.2390585","l":"temp12@cor.net","a":"test3","c":"Kom3"}]

I want to get data in table format. When is 1 row i don't have problem using:

DECLARE @JSONINPUT NVARCHAR(max)  
SET @JSONINPUT = (select top 1 cast(json_data as varchar(max)) from mytable )

IF (ISJSON(@JSONINPUT) = 1)  
BEGIN  
SELECT * from OPENJSON(@JSONINPUT )
WITH (
[Kom] nvarchar(max) '$.c',
[Date] DATETIME2  '$.d',
[User] nvarchar(150) '$.a'
);
END

and i get:

Com Date User
Kom1 2021-05-05 12:16:18.9175335 test1

But I don't know how to get data from all rows.

tsql sql json
2 Answers

The syntax, as I mentioned, is no different:

SELECT OJ.Kom,
       OJ.[Date],
       OJ.[User]
FROM dbo.YourTable YT
     CROSS APPLY OPENJSON(YT.JSONColumn)
                 WITH ([Kom] nvarchar(max) '$.c',
                       [Date] DATETIME2  '$.d',
                       [User] nvarchar(150) '$.a') OJ;
user_20299830

Use CROSS APPLY with OPENJSON

SELECT j.Kom, j.[Date], j.[User]
FROM mytable
CROSS APPLY OPENJSON(json_data)
WITH (
    [Kom] nvarchar(max) '$.c',
    [Date] DATETIME2  '$.d',
    [User] nvarchar(150) '$.a'
) AS j;
user_758520
Related