外部查詢是 UNION 一部分的 JSON 路徑的子查詢導致字元串而不是 JSON

請耐心等待 - 範常式式碼已經發生了一些事情，我將盡我所能在下面解釋它。

with ENTITIES as (
select [name] as entityName from (values ('A'), ('B'), ('C')) X([name])
),
PROPERTIES as (
select [propName] as entityName, [propValue] as propertyValue from (values ('A','1'),('A','2'),('B','3'),('UNIVERSAL','999')) Y([propName], [propValue])
),
SUBPROPERTIES as (
select [propValue] as propertyValue, [subPropValue] as subPropertyValue from (values ('1','x'),('1','y'),('2','z'),('999','xyz')) Y([propValue], [subPropValue])
)
--select * from ENTITIES
--select * from PROPERTIES

select (

   select entityName as 'entityName',
   (
       select * from 
           (
               select propertyValue as 'propertyValue',
               (
                   select subPropertyValue from SUBPROPERTIES where SUBPROPERTIES.propertyValue = PROPERTIES.propertyValue
                   FOR JSON PATH, INCLUDE_NULL_VALUES
               ) Y
               from PROPERTIES where PROPERTIES.entityName = ENTITIES.entityName 

-- For testing, comment out from here -------------------------------------------------------------------------------------
               UNION
   
               select propertyValue as 'propertyValue',
               (
                   select subPropertyValue from SUBPROPERTIES where SUBPROPERTIES.propertyValue = PROPERTIES.propertyValue
                   FOR JSON PATH, INCLUDE_NULL_VALUES
               ) Y
               from PROPERTIES where PROPERTIES.entityName = 'UNIVERSAL' 
-- For testing, stop commenting out here ----------------------------------------------------------------------------------

           ) X
       FOR JSON PATH, INCLUDE_NULL_VALUES
   ) as entityProperties
   
   from ENTITIES
   FOR JSON PATH, INCLUDE_NULL_VALUES

) jsondata

我設置了 3 個 CTE - 代表 3 個相互連結的表。一個實體記錄（如“A”）可以有多個屬性（如“1”和“2”），每個屬性記錄可以有多個子屬性（如“1”有子屬性“x”和“y”）。

現在主查詢只是嘗試建構一個 JSON 對象，該對象將返回所有實體及其所有連結的屬性和連結的子屬性。

所以第一個選擇列只給你 entityName - 一個頂級查詢。

下一列是子查詢，用於獲取與給定 ENTITIES 記錄相關的所有 PROPERTIES 值。您會看到它返回帶有別名“propertyValue”的東西——當然這將是一組值——連結到目前實體記錄的每個 PROPERTIES 記錄的一個值。

子查詢有第二列，它是另一個子查詢 - 到 SUBPROPERTIES 表 - 類似於上面。

現在問題來了——在這個中間級別（報告屬性），我實際上想從 UNION 獲取數據。

（在這個例子中，聯合中的第二個查詢正在查詢同一個源表 - 所以我可以通過將 WHERE 子句更改為來避免聯合or PROPERTIES.entityName = 'UNIVERSAL'- 但在現實世界中，我從兩個不同的表中獲取數據，所以我想與 UNION 聚合）。

我知道，當FOR XML PATH與 UNION 一起使用時，您需要將所有 UNION-ed SELECT 包裝在括號中並給它一個別名（在本例中為 X），然後從中選擇 - 我已經用select * from. （忽略這select *是不好的做法 - 這不是重點）。

這種對 UNION 的方法工作得很好——只要已經被 UNION 的查詢不包含子查詢——但正如你在這裡看到的，我有子查詢。

所以 - 看看我試圖說明什麼，如果您註釋掉 UNION（和第二個選擇），您將根據下圖獲得結構良好的 JSON。我已經強調了在結構化 JSON 中正確顯示 SUBPROPERTIES 的位置。

但是，當我添加 UNION 時，這些子查詢返回字元串，而不是 JSON，如下圖所示。我已經突出顯示了字元串的位置。

我嘗試了各種方法來添加 JSON_QUERY 函式（在某些情況下建議將字元串解析為 JSON），但是 a) 未能解決問題，並且 b) 沒有實際需要完全相同的數據時存在不是聯合。

我也嘗試過各種方法來select * from處理最低級別的子查詢 - 無濟於事。

提前致謝。

問題似乎是 SQL 在合併時將內部 SELECT 中的偽欄位視為文本而不是 JSON。當外部 FOR JSON 應用於此偽欄位時，它會嘗試轉義文本欄位中的特殊字元。有關更多資訊，請參閱此連結。

您可以使用JSON_QUERY函式來否定這一點，本質上是強制 SQL Server 將 JSON 欄位視為 JSON 而不是文本。請參閱此小提琴以獲取工作範例。

查詢（注意兩次使用 JSON_QUERY，每個內部 SELECT 一次）：

with ENTITIES as (
select [name] as entityName from (values ('A'), ('B'), ('C')) X([name])
),
PROPERTIES as (
select [propName] as entityName, [propValue] as propertyValue from (values ('A','1'),('A','2'),('B','3'),('UNIVERSAL','999')) Y([propName], [propValue])
),
SUBPROPERTIES as (
select [propValue] as propertyValue, [subPropValue] as subPropertyValue from (values ('1','x'),('1','y'),('2','z'),('999','xyz')) Y([propValue], [subPropValue])
)

select entityName as 'entityName',
JSON_QUERY(
   (select propertyValue, JSON_QUERY(Y) AS subPropertyValue from 
       (
           select propertyValue as 'propertyValue',
           (
               select subPropertyValue from SUBPROPERTIES where SUBPROPERTIES.propertyValue = PROPERTIES.propertyValue
               FOR JSON PATH, INCLUDE_NULL_VALUES
           ) Y
           from PROPERTIES where PROPERTIES.entityName = ENTITIES.entityName 

-- For testing, comment out from here -------------------------------------------------------------------------------------
           UNION

           select propertyValue as 'propertyValue',
           (
               select subPropertyValue from SUBPROPERTIES where SUBPROPERTIES.propertyValue = PROPERTIES.propertyValue
               FOR JSON PATH, INCLUDE_NULL_VALUES
           ) Y
           from PROPERTIES where PROPERTIES.entityName = 'UNIVERSAL' 
-- For testing, stop commenting out here ----------------------------------------------------------------------------------

       ) X
   FOR JSON PATH, INCLUDE_NULL_VALUES
)
) as entityProperties
from ENTITIES
FOR JSON PATH, INCLUDE_NULL_VALUES

結果

[
 {
   "entityName": "A",
   "entityProperties": [
     {
       "propertyValue": "1",
       "subPropertyValue": [
         {
           "subPropertyValue": "x"
         },
         {
           "subPropertyValue": "y"
         }
       ]
     },
     {
       "propertyValue": "2",
       "subPropertyValue": [
         {
           "subPropertyValue": "z"
         }
       ]
     },
     {
       "propertyValue": "999",
       "subPropertyValue": [
         {
           "subPropertyValue": "xyz"
         }
       ]
     }
   ]
 },
 {
   "entityName": "B",
   "entityProperties": [
     {
       "propertyValue": "3",
       "subPropertyValue": null
     },
     {
       "propertyValue": "999",
       "subPropertyValue": [
         {
           "subPropertyValue": "xyz"
         }
       ]
     }
   ]
 },
 {
   "entityName": "C",
   "entityProperties": [
     {
       "propertyValue": "999",
       "subPropertyValue": [
         {
           "subPropertyValue": "xyz"
         }
       ]
     }
   ]
 }
]

引用自：https://dba.stackexchange.com/questions/266738