Issue with dynamic SQL to get JSON properties as columns when JSON is NULL

Question

I am trying to select JSON properties as column. The properties that needs to be selected are stored in some other table

CREATE TABLE [Templates](
    [ID] [int] NOT NULL,
    [Template] [nvarchar](max)
)

INSERT INTO Templates(ID,Template)
VALUES
(1,'{"FirstName":"foo"}'),
(2,'{"FirstName":"joe","LastName":"dow"}'),
(3,'{"LastName":"smith","Address":"1234 Test Drive"}'),
(4,NULL)


Declare @SQL NVARCHAR(max)

--For brevity purpose i am hardcoding these property names. In reality column names will come from another table
DECLARE @properties NVARCHAR(max) = '[Address],[FirstName],[LastName]'

Set @SQL  = '
Select '+ @properties +'
 From  (
        Select T.ID
              ,j.[Key]
              ,j.[Value]
         From  Templates T
         Cross Apply OpenJSON(T.Template) AS j
       ) src
Pivot ( max(value) for [Key] in ('+ @properties +') ) pvt 
'
Exec(@SQL)

SQL Fiddle

The code above is mostly working except for the Last row where Template is NULL. (ID = 4)
Expected: The query should return 4 rows and for the last row, all columns should be null except ID.

Change CROSS APPLY to OUTER APPLY.

Thom A
– Thom A ♦

2020-08-05 21:50:30 +00:00
Commented Aug 5, 2020 at 21:50 — Thom A
– Thom A ♦, Commented Aug 5, 2020 at 21:50

Thom A · Accepted Answer · 2020-08-05 22:00:53Z

1

CROSS APPLY requires rows to be returned, however, Cross Apply OpenJSON(NULL) AS j returns no rows. Use OUTER APPLY:

' ...
OUTER APPLY OpenJSON(T.Template) AS j
...'

EXEC sp_executesql @SQL;

Note I also use sp_executesql as you can't parametrise EXEC (@SQL)

db<>fiddle

edited Aug 5, 2020 at 22:00

answered Aug 5, 2020 at 21:51

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Issue with dynamic SQL to get JSON properties as columns when JSON is NULL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related