Question 111016 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Get Array Index from JSON data SQL Server

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

Tags:

2

Answers


  1. 0

    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. 0

    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