Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

Why does my table Alias get removed when using OpenJSON?

xapguh5xapguh5 Posts: 2 New member
As I've been learning how to shred Json objects in SQL, I'm finding some strange behavior with SQL Prompt. 

I have Json  with nested objects, that I shred like this: 
DECLARE @Json NVARCHAR(MAX) = N'
[
   {
      "ProductID":594,
      "ProductName":"Sample",
      "OrgID":5876,
      "Numerics":{
         "Q1":"5",
         "Q2":"8",
         "Q3":"9",
         "Q4":"9",
         "Q5":"8"        
      }
   }
]'

SELECT oj.ProductID
     , oj.ProductName
     , oj.OrgID
     , p.[Key] AS Question
     , p.Value AS Answer
FROM
    OPENJSON(@Json)
    WITH ( ProductID INT
         , ProductName NVARCHAR(50)
         , OrgID INT
         , Numerics NVARCHAR(MAX) AS JSON ) AS oj
CROSS APPLY OPENJSON(oj.Numerics) AS p

If I format my code using Ctrl K+Y, the alias for "oj" drops in my CROSS APPLY.  This is particularly problematic when I have several nested objects that I CROSS APPLY in the same query and all aliases drop after formatting. 

Do you have any suggestions on how I can keep the table Alias alive in my following Joins?

Answers

Sign In or Register to comment.