By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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"
}
]
}');
1 rows affected
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
| 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 |