sqlsql-serverdatabasejson-queryjson-value

How to loop array of objects from table column?


Table columns :

id
---------
details

My table column details has json object like

"data" : [ {
   "name" : "luke",
   "dob" : "12-10-90",
   "addr" : "sample1",
 }, 
{
    "name" : "sam",
    "dob" : "12-10-88",
    "addr" : "sample2"
   }
]

I want to write a query which will give me records like below :

| id   | name|  dob      |addr       |
|:-----|:----|:--- ------|:----------|
| 1    | luke|  12-10-90 |  sample1  |
| 1    | sam |  12-10-88 |  sample2  |

I tried with

select 
    ID,
    JSON_VALUE(DETAILS, '$.data[0].name') , 
    JSON_VALUE(DETAILS, '$.data[0].dob') , 
        JSON_VALUE(DETAILS, '$.data[0].addr')
from 
    users;

RESULT WITH COUNT :

id  cnt  name       dob       addr   
--  ---  ---------  --------  -------
 1    5  luke       12-10-90  sample1
 1    5  sam        12-10-88  sample2
 2    5  awd        12-10-90  sample1
 2    5  awdawdm    12-10-88  sample2
 2    5  sevsevsev  12-10-88  sample2

EXPECTED

id  cnt  name       dob       addr   
--  ---  ---------  --------  -------
 1    2  luke       12-10-90  sample1
 1    2  sam        12-10-88  sample2
 2    3  awd        12-10-90  sample1
 2    3  awdawdm    12-10-88  sample2
 2    3  sevsevsev  12-10-88  sample2

Solution

  • To get all the objects from json array along with other columns you can use OpenJson() and Cross Apply as below:

     create table users (id int, details nvarchar(max));
     insert into users values (1,N'{
                   "data":[
                           {
                            "name" : "luke",
                            "dob" : "12-10-90",
                            "addr" : "sample1"
                           },
                           {
                             "name" : "sam",
                             "dob" : "12-10-88",
                             "addr" : "sample2"
                           }
                          ]
                  }');
    

    Query:

     SELECT u.id,count(details.name)over() cnt, details.name, details.dob,details.addr,details.*
     FROM users u CROSS APPLY OPENJSON (u.details,N'$.data') 
               WITH (   
                       
                     [Name] VARCHAR(100) '$.name',
                     dob VARCHAR(10) '$.dob',
                     addr VARCHAR(100) '$.addr'
                    
                   
      ) AS details
    

    Output:

    id cnt name dob addr Name dob addr
    1 2 luke 12-10-90 sample1 luke 12-10-90 sample1
    1 2 sam 12-10-88 sample2 sam 12-10-88 sample2

    db<>fiddle here

    Your code is working @DLV. Please check. There were problem with data formatting.

     create table users (id int, details nvarchar(max));
     insert into users values (1,N'{"data" : 
     [ {     "name" : "luke",     "dob" : "12-10-90",     "addr" : "sample1",   },  
     {      "name" : "sam",      "dob" : "12-10-88",      "addr" : "sample2"    }  
     ]}');
    
    
    
    
     select 
         ID,
         JSON_VALUE(DETAILS, '$.data[0].name') , 
         JSON_VALUE(DETAILS, '$.data[0].dob') , 
         JSON_VALUE(DETAILS, '$.data[0].addr')
     from 
         users;
    

    Output:

    ID (No column name) (No column name) (No column name)
    1 luke 12-10-90 sample1

    db<>fiddle here