Reputation: 45
Using SQL 2016 JSON built in functions
Table: Users Columns (ID int, FirstName varchar(50), LastName varchar(50)
Data
3009 Emily Manners
3010 Joanne Hernandez
3011 Kelly Kleiner
3012 Alexis Frederick
3013 Dietric Singleton
3018 Ashley Ely
3021 Jeralynn Campbell-Triplett
3026 Lauren Zinnerman
3027 Christopher Correa
3028 Stefanie MontalvoCruz
Desired results -> temp table
ID JSON_DATA
3009 {"ID":3009,"FirstName":"Emily","LastName":"Manners"}
3010 {"ID":3010,"FirstName":"Joanne","LastName":"Hernandez"}
Trying to use:
SELECT
[ID],
[FirstName],
[LastName]
FROM Emp
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
Upvotes: 1
Views: 658
Reputation: 1702
This form may be more readable than using cross apply in some circumstances.
In cases I've tried execution plans are always identical but one may perform better than the other in any given situation.
Declare @Emp table (ID int,FirstName varchar(50),LastName varchar(50))
Insert into @Emp values
(3009,'Emily' ,'Manners'),
(3010,'Joanne','Hernandez'),
(3011,'Kelly' ,'Kleiner')
Select A.ID
,(Select A.ID,A.FirstName,A.LastName
FOR JSON PATH, INCLUDE_NULL_VALUES,
WITHOUT_ARRAY_WRAPPER) AS JSON_Data
From @Emp A
Upvotes: 0
Reputation: 82020
With the help of a CROSS APPLY
Declare @Emp table (ID int,FirstName varchar(50),LastName varchar(50))
Insert into @Emp values
(3009,'Emily' ,'Manners'),
(3010,'Joanne','Hernandez'),
(3011,'Kelly' ,'Kleiner')
Select A.ID
,B.JSON_Data
From @Emp A
Cross Apply (
Select JSON_Data = (Select A.ID,A.FirstName,A.LastName FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER)
) B
Returns
ID JSON_Data
3009 {"ID":3009,"FirstName":"Emily","LastName":"Manners"}
3010 {"ID":3010,"FirstName":"Joanne","LastName":"Hernandez"}
3011 {"ID":3011,"FirstName":"Kelly","LastName":"Kleiner"}
Upvotes: 3