CODEWITHSUNDEEP
sqlsql-serverjsont-sqlsql-server-2016
DennisT
DennisT

Reputation: 45

Return Key and JSON String from SQL 2016 Query

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

Answers (2)

bielawski
bielawski

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

John Cappelletti
John Cappelletti

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

Related Questions