How to return an array in a JSON object from SQL Server

Question

I am trying to get a JSON expression that contains a nested title (cars.) based on another column (ID).

Original table:

The basic item has the following structure:

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

Each ID should have a single object.

If ID appears twice, then the item title "cars" should be an array:

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

This is the SQL code I tried:

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

And this is the result I got:

[
   {
      "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"
      }
   }
]

The result I would like to end up with:

[
   {
      "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"
      }
   }
]

Code to generate the sample table:

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');

Appreciate any help, Tamir

Answer 1

Solution as given by @lptr in a comment:

select
  o.FirstName,
  o.ID,
  json_query((
      select i.CarLicense, i.CarType, i.CarSystem as SourceSystem
      from dbo.cars as i
      where i.ID = o.ID
      for json path
    ), case count(*) when 1 then '$[0]' else '$' end) as cars
from dbo.cars o
group by
  o.firstname,
  o.id
order by
  o.firstname
for json path;

db<>fiddle

This uses a correlated subquery to return the array. The case ensures that if there is only one item then a single object is returned instead of an array. This works on SQL Server 2017+.

Answer 2

You can embed an array of objects into the cars property by using a subquery inside json_query(), such as with the following:

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;