如何從SQLServer回傳JSON物件中的陣列

我正在嘗試獲取一個 JSON 運算式，其中包含基于另一列 (ID) 的嵌套標題 (cars.)。

原表：

基本項具有以下結構：

"FirstName":"Eli",
      "ID":123,
      "cars":[
          {
             "CarLicense":222,
             "CarType":"AC",
             "SourceSystem":"CompanyCars"
          }

每個 ID 應該有一個物件。

如果 ID 出現兩次，則專案標題“cars”應該是一個陣列：

{
      "FirstName":"Eli",
      "ID":123,
      "cars":[
          {
             "CarLicense":222,
             "CarType":"AC",
             "SourceSystem":"CompanyCars"
          },
          {
             "CarLicense":111,
             "CarType":"ACAA",
             "SourceSystem":"GuestCars"
          }
      ]
   }

這是我嘗試的 SQL 代碼：

SELECT 
    FirstName, 
    ID,
    CarLicense AS 'cars.CarLicense',
    CarType AS 'cars.CarType',
    CarSystem AS 'cars.SourceSystem'
FROM 
    [Tamir].[dbo].[cars]
FOR JSON PATH;

這是我得到的結果：

[
   {
      "FirstName":"Eli",
      "ID":123,
      "cars":{
         "CarLicense":222,
         "CarType":"AC",
         "SourceSystem":"CompanyCars"
      }
   },
   {
      "FirstName":"Eli",
      "ID":123,
      "cars":{
         "CarLicense":111,
         "CarType":"ACAA",
         "SourceSystem":"GuestCars"
      }
   },
   {
      "FirstName":"Gal",
      "ID":789,
      "cars":{
         "CarLicense":444,
         "CarType":"AB",
         "SourceSystem":"PrivateCars"
      }
   },
   {
      "FirstName":"Tamir",
      "ID":456,
      "cars":{
         "CarLicense":333,
         "CarType":"AX",
         "SourceSystem":"PrivateCars"
      }
   },
   {
      "FirstName":"Tamir",
      "ID":4561,
      "cars":{
         "CarLicense":555,
         "CarType":"B",
         "SourceSystem":"GuestCars"
      }
   }
]

我想得到的結果是：

[
   {
      "FirstName":"Eli",
      "ID":123,
      "cars":[
         {
            "CarLicense":222,
            "CarType":"AC",
            "SourceSystem":"CompanyCars"
         },
         {
            "CarLicense":111,
            "CarType":"ACAA",
            "SourceSystem":"GuestCars"
         }
      ]
   },
   {
      "FirstName":"Gal",
      "ID":789,
      "cars":{
         "CarLicense":444,
         "CarType":"AB",
         "SourceSystem":"PrivateCars"
      }
   },
   {
      "FirstName":"Tamir",
      "ID":456,
      "cars":{
         "CarLicense":333,
         "CarType":"AX",
         "SourceSystem":"PrivateCars"
      }
   },
   {
      "FirstName":"Tamir",
      "ID":4561,
      "cars":{
         "CarLicense":555,
         "CarType":"B",
         "SourceSystem":"GuestCars"
      }
   }
]

生成示例表的代碼：

DROP TABLE IF EXISTS cars;

CREATE TABLE cars
(
   FirstName  VARCHAR(5),
   ID         INTEGER,
   CarLicense INTEGER,
   CarType    VARCHAR(4),
   CarSystem  VARCHAR(11)
);

INSERT INTO cars (FirstName, ID, CarLicense, CarType, CarSystem)  
VALUES ('Eli', 123, 222, 'AC', 'CompanyCars');

INSERT INTO cars (FirstName, ID, CarLicense, CarType, CarSystem) 
VALUES ('Eli', 123, 111, 'ACAA', 'GuestCars');

INSERT INTO cars (FirstName, ID, CarLicense, CarType, CarSystem) 
VALUES ('Gal', 789, 444, 'AB', 'PrivateCars');

INSERT INTO cars (FirstName, ID, CarLicense, CarType, CarSystem) 
VALUES ('Tamir', 456, 333, 'AX', 'PrivateCars');

INSERT INTO cars (FirstName, ID, CarLicense, CarType, CarSystem) 
VALUES ('Tamir', 4561, 555, 'B', 'GuestCars');

感謝任何幫助，塔米爾

uj5u.com熱心網友回復：

您可以使用內部的子查詢將物件陣列嵌入到cars屬性中json_query()，例如：

select
  FirstName, 
  ID,
  json_query((
    select
      CarLicense,
      CarType,
      CarSystem as SourceSystem
    from dbo.cars carsInner
    where carsInner.ID=carsOuter.ID
    for json path
  )) as cars
from dbo.cars carsOuter
for json path;

標籤：

上一篇：如何在json.loadsPandaDataframe期間轉換錯誤時列印索引（行號）

下一篇：如何有效地修復從熊貓資料框轉換的JSON檔案