{
"Name": ["dokumen_1","dokumen_2","dokumen_3","dokumen_4"],
"Date": [0,0,0,0],
"Progress": [0,0,0,0]
}
I want to fetch Date and Progress value according to Name position.
Add a comment
|
2 Answers
I changed the date and progress values for a better illustration
NOTE: in 2016 the JSON_VALUE has to be a literal
Example
Declare @JSON varchar(max) = '
{
"Name":["dokumen_1","dokumen_2","dokumen_3","dokumen_4"],
"Date":[1,2,3,4],
"Progress":[11,12,13,12]
}'
Select Name = value
,Date = JSON_VALUE(@JSON,'$.Date['+[key]+']')
,Progress = JSON_VALUE(@JSON,'$.Progress['+[key]+']')
From openjson(@JSON,N'$.Name')
Results
Name Date Progress
dokumen_1 1 11
dokumen_2 2 12
dokumen_3 3 13
dokumen_4 4 12
2 Comments
Irvan Affandy
Thank you very much, and what if I will only retrieve data based on the name "dokumen_3"
John Cappelletti
@IrvanAffandy Just add WHERE value='dokumen_3'
Another possible approach is a combination of OPENJSON() with default schema and appropriate JOINs. Of course, you need at least SQL Server 2016 to use the built-in JSON support.
DECLARE @json varchar(max) = '
{
"Name":["dokumen_1","dokumen_2","dokumen_3","dokumen_4"],
"Date":[101,102,103,104],
"Progress":[201,202,203,204]
}'
SELECT n.[value] AS Name, d.[value] AS Date, p.[value] AS Progress
FROM OPENJSON(@json, '$.Name') n
LEFT JOIN OPENJSON(@json, '$.Date') d ON n.[key] = d.[key]
LEFT JOIN OPENJSON(@json, '$.Progress') p ON n.[key] = p.[key]