how to add key-value pair in json root node and convert it into table using SQL server

Question

I have table people and it's maintain Four column which is Name ,TagName ,Value , Location. I want to convert the tagname and value in json with name and location column as rootnode (Name & location same for multiple records)

Need output as :

{
    "{"Name":"EMP1","Location":"mumbai"}": [
        {
            "TagName": "1",
            "Value": "844.17769999999996"
        },
        {
            "TagName": "abc",
            "Value": "837.43679999999995"
        },
        {
            "TagName": "pqr",
            "Value": "0"
        },
        {
            "TagName": "XYZ",
            "Value": "1049.2429999999999"
        }
    ]
}

please check the below query, In which I am trying to create json string using json path but stuck in root node.

SELECT  TagName
        ,Value
FROM dbo.people
FOR JSON PATH, ROOT('')---

when I convert the above json into tabular format, required output as :

Name | Location |TagName| Value 
EMP1 | Mumbai   |1      |    844.17769999999996|
EMP1 | Mumbai   |abc    |    837.43679999999995|
.....

Answer 1

Your expected output is not a valid JSON, but you are probably looking for something like this:

Table:

CREATE TABLE People (
   [Name] varchar(10),
   [Location] varchar(50),
   [TagName] varchar(3), 
   [Value] numeric(20, 14)
)
INSERT INTO People ([Name], [Location], [TagName], [Value])
VALUES
   ('EMP1', 'Mumbai', '1',   844.17769999999996),
   ('EMP1', 'Mumbai', 'abc', 837.43679999999995),
   ('EMP2', 'Mumbai', 'abc', 837.43679999999995)

Statement:

SELECT DISTINCT p.[Name], p.[Location], c.Items
FROM People p
CROSS APPLY (
   SELECT [TagName], [Value]
   FROM People 
   WHERE [Name] = p.[Name] AND [Location] = p.[Location]
   FOR JSON AUTO
) c (Items)
FOR JSON PATH

Result:

[
   {
      "Name":"EMP1",
      "Location":"Mumbai",
      "Items":[
         {
            "TagName":"1",
            "Value":844.17769999999996
         },
         {
            "TagName":"abc",
            "Value":837.43679999999995
         }
      ]
   },
   {
      "Name":"EMP2",
      "Location":"Mumbai",
      "Items":[
         {
            "TagName":"abc",
            "Value":837.43679999999995
         }
      ]
   }
]

If you want to parse the generated JSON, you need to use OPENJSON() twice:

Generated JSON:

DECLARE @json varchar(max) = N'[
   {
      "Name":"EMP1",
      "Location":"Mumbai",
      "Items":[
         {
            "TagName":"1",
            "Value":844.17769999999996
         },
         {
            "TagName":"abc",
            "Value":837.43679999999995
         }
      ]
   },
   {
      "Name":"EMP2",
      "Location":"Mumbai",
      "Items":[
         {
            "TagName":"abc",
            "Value":837.43679999999995
         }
      ]
   }
]'

Statement:

SELECT j1.Name, j1.Location, j2.TagName, j2.Value
FROM OPENJSON(@json) WITH (
   [Name] varchar(10) '$.Name',
   [Location] varchar(50) '$.Location',
   [Items] nvarchar(max) '$.Items' AS JSON
) j1
OUTER APPLY OPENJSON(j1.Items) WITH (
   [TagName] varchar(3) '$.TagName', 
   [Value] numeric(20, 14) '$.Value'
) j2