Karol karol
Karol karol

Reputation: 83

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

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

Answers (2)

squillman
squillman

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

Thom A
Thom A

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

Related Questions