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