Reputation: 83
I have in my table rows with JSON same as a example:
json_data |
---|
[{"d":"2021-05-05T12:16:18.9175335","l":"[email protected]","a":"test1","c":"Kom1"}] |
[{"d":"2021-05-05T12:16:37.7258608","l":"[email protected]","a":"test2","c":"Kom2"}] |
[{"d":"2021-05-05T12:17:30.2390585","l":"[email protected]","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.
Upvotes: 1
Views: 695
Reputation: 13641
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;
Upvotes: 2
Reputation: 95564
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;
Upvotes: 2