skip to Main Content
{
    "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.

2

Answers


  1. 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
    
    Login or Signup to reply.
  2. 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]
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search