Improve code completion support for OPENJSON / WITH fields
When OPENJSON along with WITH to list fields to pull from the JSON data the fields are not visible for code completion purposes. For example in the query below there is no code completion for B.* in the select list and the fields need to be entered manually.
SELECT PD.ProductDimId AS ProductDim
, [Date] = CAST(JSONVALUE(J.JSONData,'$.fields.trandate') AS DATE)
, TONumber = JSONVALUE(J.JSONData,'$.fields.tranid')
, TransactionInternalId = J.InternalId
, [Status] = JSONVALUE(J.JSONData,'$.fields.status')
, Line = B.line
, B.Quantity
, TOCurrentValueUSD = B.CurrentUSDValue
, TOLandedCostPerItem = b.LandedCostPerUnit
, FromLocation = CASE JSONVALUE(J.JSONData,'$.fields.location') WHEN 16 THEN 'In Transit/On Water' ELSE '' END
, ToLocation = CASE JSONVALUE(J.JSONData,'$.fields.transferlocation') WHEN 15 THEN 'Receipt' ELSE '' END
, Container = JSONVALUE(J.JSONData,'$.fields.custbodypcapcontainernumber')
, DateOfArrival = CAST(JSONVALUE(J.JSONData,'$.fields.custbodypcapactualarrival') AS DATE)
-- , ItemJSON = a.Value
-- , A.*
FROM NetSuite.JSONRecords J
CROSS APPLY OPENJSON(JSONQuery(J.JSONData,'$.sublists.item')) AS A
CROSS APPLY OPENJSON(A.value,'$')
WITH (SKU VARCHAR(50) 'lax $.itemdisplay'
,Quantity INT 'lax $.quantity'
,QuantityFulfilled INT 'lax $.quantityfulfilled'
,QuantityReceived INT 'lax $.quantityreceived'
,Line INT 'lax $.line'
,CurrentUSDValue NUMERIC(18,2) 'lax $.custcolcurrentvalue'
,LandedCostPerUnit NUMERIC(18,2) 'lax $.custcollandedcostunit'
) AS B
JOIN [SVR-DB-07].DW1.dbo.ProductDim PD ON pd.ProductName = b.sku
WHERE J.RecordType = 'transferOrder'
AND a.[key] != 'currentline'
AND JSONVALUE(J.JSONData,'$.fields.location') = 16
AND JSONVALUE(J.JSONData,'$.fields.transferlocation') = 15
AND JSON_VALUE(J.JSONData,'$.fields.tranid') = 2816