Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.
Why does my table Alias get removed when using OpenJSON?
xapguh5
Posts: 2 New member
in SQL Prompt
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:
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?
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?
Tagged:
Answers