Get Array Index from JSON data SQL Server-CodePudding

{
    "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.

CodePudding user response：

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

CodePudding user response：

Another possible approach is a combination of OPENJSON() with default schema and appropriate JOINs:

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]