This is my table [Type]
and this is my table [Value]
My sql script is
SELECT T.TypeId, V.ValueId
FROM [dbo].[Type] AS T
INNER JOIN [dbo].[Value] AS V ON V.[TypeId] = T.[TypeId]
FOR JSON PATH
It will get the result
[
{
"TypeId":"TypeId_1",
"ValueId":"ValueId_1"
},
{
"TypeId":"TypeId_1",
"ValueId":"ValueId_2"
},
{
"TypeId":"TypeId_1",
"ValueId":"ValueId_3"
},
{
"TypeId":"TypeId_2",
"ValueId":"ValueId_4"
},
{
"TypeId":"TypeId_2",
"ValueId":"ValueId_5"
}
]
But i want to let the TypeId as json property name, and its value is an array of ValueId, like:
{
"TypeId_1": [
"ValueId_1",
"ValueId_2",
"ValueId_3"
],
"TypeId_2": [
"ValueId_4",
"ValueId_5"
]
}
How to adjust my sql script?

