Return Key and JSON String from SQL 2016 Query

Question

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

John Cappelletti · Accepted Answer · 2017-03-15 18:25:53Z

3

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"}

edited Mar 15, 2017 at 18:25

answered Mar 15, 2017 at 18:11

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SqlZim Over a year ago

Heya John chat.stackoverflow.com/rooms/138432/…

bielawski · Accepted Answer · 2017-05-31 14:50:56Z

0

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

answered May 31, 2017 at 14:50

bielawski

1,74017 silver badges23 bronze badges

Collectives™ on Stack Overflow

Return Key and JSON String from SQL 2016 Query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related