JSON_Query with For Json Path

Please see the table given below. The table contains the json string and need to create a json array with those json string. But When I use JSON_Query and For Json Path it adds additional header. (Alias name or the source column name). How to generate the json array without alias name or source column name.

Please see the example given below.

DECLARE @jsonTbl TABLE (id INT,json VARCHAR(MAX))

INSERT INTO @jsonTbl (id,json) VALUES (1,'{"id":"1A", "names":{"firstname":"Name1"}}')
INSERT INTO @jsonTbl (id,json) VALUES (1,'{"id":"2A", "names":{"firstname":"Name2"}}')

SELECT JSON_QUERY(json) AS 'someName' 
  FROM @jsonTbl 
   FOR JSON AUTO

   --When I use the above select query it returns the data as
   [{"SomeName":{"id":"1A", "names":{"firstname":"Name1"}}},{"SomeName":{"id":"2A", "names": 
   {"firstname":"Name2"}}}]

   Formatted JSON

   ```[
     {
         "someName":{
         "id":"1A",
         "names":{"firstname":"Name1"}
      }
   },
   {
      "someName":{
         "id":"1B",
         "names":{
            "firstname":"Name1"
         }
      }
   }
]

--But need the result as follows. Do not need someName

[
   {
      "id":"1A",
      "names":{
         "firstname":"Name1"
      }
   },
   {
      "id":"2A",
      "names":{
         "firstname":"Name2"
      }
   }
]```

Answer

You can use OPENJSON() together with CROSS APPLY

SELECT j.[id], j.[names] 
  FROM @jsonTbl t
 CROSS APPLY OPENJSON(t.json, '$') WITH ([id]    VARCHAR(100),
                                         [names] NVARCHAR(MAX) AS JSON) j
FOR JSON AUTO

Demo

Advertisement

Answer