How to loop array of objects from table column?

Question

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

But I've to hardcoded the index and its still not giving two rows. — DLV, Mar 16 '21 at 03:56
@DaleK I've tried with json_value but its not looping on its own depending on number of elements in the json object and also how can I have two different rows for the same id? — DLV, Mar 16 '21 at 04:00

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-04-15T17:55:06.987

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

A quick question : Is there any way to get a count of number of items within the data? I wanted have a where condition for more than 5 elements. — DLV, Mar 16 '21 at 21:04
Like in your example we have two records inside 'data' attribute. How can I get a count of these records? — DLV, Mar 16 '21 at 21:13
If I add one more record with three elements in data attribute I get above response. What I am looking for is ID 1 should have count 2 where ID 2 should have count 3. Currently both saying count as 5. — DLV, Mar 16 '21 at 22:45

Annamalai D · Answer 2 · 2021-03-16T05:21:14.583

You can loop through every record and use OPENJSON to achieve this.

DECLARE @Id int
,@details NVARCHAR(MAX)

DROP TABLE IF EXISTS #Result
CREATE TABLE #Result (Id INT, [Name] VARCHAR(100),dob VARCHAR(10),addr VARCHAR(100))

DECLARE json_cursor CURSOR FOR 
SELECT Id,details 
FROM #DetailsTable  -- Replace with your table

OPEN json_cursor  
FETCH NEXT FROM json_cursor INTO @Id,@details

WHILE @@FETCH_STATUS = 0  
BEGIN  
      
      BEGIN
      INSERT INTO #Result
        SELECT @Id AS Id,*
            FROM OPENJSON(@details,N'$.data')
            WITH (   
                  
                            [Name] VARCHAR(100) '$.name',
                            dob VARCHAR(10) '$.dob',
                            adrr VARCHAR(100) '$.addr'
               
              
             ) 
      END

      FETCH NEXT FROM json_cursor INTO @Id,@details 
END 

CLOSE json_cursor  
DEALLOCATE json_cursor 

SELECT * FROM #Result

make sure your json object is enclosed in '{}' like below

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

How to loop array of objects from table column?

2 Answers2