In SQL Server I have a table with columns (a, b, c, name1, address1, phonenumber1, name2, address2, phonenumber2). I want to generate JSON for a row like:
{
"a" : value_column_a,
"b" : value_column_b,
"c" : value_column_c,
"contacts": [
{
"name" : name1,
"phone_num": phonenumber1,
"address": address1
},
{
"name" : name2,
"phone_num": phonenumber2,
"address": address2
}
]
}
Please, help me! I took some hours, but I could not solved it.
Embedding arrays of objects into JSON on Microsoft SQL Server is achieved by using json_query() over a subquery of the nested data, such as the following...
/*
* Data setup...
*/
create table dbo.Thingiebob (
[a] varchar(14),
[b] varchar(14),
[c] varchar(14),
[name1] varchar(14),
[address1] varchar(14),
[phonenumber1] varchar(14),
[name2] varchar(14),
[address2] varchar(14),
[phonenumber2] varchar(14)
);
insert dbo.Thingiebob ([a], [b], [c], [name1], [address1], [phonenumber1], [name2], [address2], [phonenumber2])
values
('value_column_a', 'value_column_b', 'value_column_c', 'name1', 'address1', 'phonenumber1', 'name2', 'address2', 'phonenumber2');
/*
* Example query...
*/
select
[a],
[b],
[c],
json_query((
select *
from (
select name1, phonenumber1, address1
union
select name2, phonenumber2, address2
) unions ([name], [phone_num], [address])
for json path
), '$') as [contacts]
from dbo.Thingiebob
for json path;
Which yields the following output...
[
{
"a": "value_column_a",
"b": "value_column_b",
"c": "value_column_c",
"contacts": [
{
"name": "name1",
"phone_num": "phonenumber1",
"address": "address1"
},
{
"name": "name2",
"phone_num": "phonenumber2",
"address": "address2"
}
]
}
]
