SQL Server 2016 set data value as json property name

Viewed 139

This is my table [Type]

TypeTable

and this is my table [Value]

ValueTable

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?

0 Answers
Related