Flatten JSON object using SQL Server

Question

I have the following data in SQL Server 2017. I am trying to achieve the following using JSON for SQL Server:

1. Output JSON key-value pairs (no nesting)
2. Use nulls if specific values are missing
3. Use column value as part of key name for some fields

create table Items (
    itemID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    name varchar(200)
)
create table Orders (
    orderID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    purchaseDate datetime,
    purchasedBy varchar(max)
)
create table Purchases (
    purchaseID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    orderID int FOREIGN KEY REFERENCES Orders(orderID),
    itemID int FOREIGN KEY REFERENCES Items(itemID),
    qty int,
    amount int
)

insert into Items values ('Apples'), ('Oranges'), ('Bananas');
insert into Orders values 
(dateadd(day, -5, getdate()), 'Person1'),
(dateadd(day, -4, getdate()), 'Person2'),
(dateadd(day, -3, getdate()), 'Person3')
;           
insert into Purchases values
 (1, 1, 1, 100),
 (1, 2, 2, 150),
 (1, 3, 5, 200),
 (2, 1, 2, 200),
 (2, 3, 1, 40),
 (3, 2, 1, 75)
;

I have this sql query but it doesn't match the output I'm expecting:

 select 
    o.orderID as 'orderID',
    max(o.purchaseDate) as 'purchaseDate',
    max(o.purchasedBy) as 'purchasedBy',
    (
        select 
            i.name as 'name',
            p.qty as 'qty',
            p.amount as 'amount'
        from
            Purchases p
            inner join Items i on p.itemID = i.itemID
        where o.orderID = p.orderID
        for json path
    )t
 from
    Orders o
 group by o.orderID
 for json path

Output:

[
  {
    "orderID": 1,
    "purchaseDate": "2023-11-05T20:32:08.870",
    "purchasedBy": "Person1",
    "t": [
      { "name": "Apples", "qty": 1, "amount": 100 },
      { "name": "Oranges", "qty": 2, "amount": 150 },
      { "name": "Bananas", "qty": 5, "amount": 200 }
    ]
  },
  {
    "orderID": 2,
    "purchaseDate": "2023-11-06T20:32:08.870",
    "purchasedBy": "Person2",
    "t": [
      { "name": "Apples", "qty": 2, "amount": 200 },
      { "name": "Bananas", "qty": 1, "amount": 40 }
    ]
  }
]

Expected Output (all key-value pairs, no nesting, populate missing values with nulls):

[
    {
      "orderID": 1,
      "purchaseDate": "2023-11-05T20:32:08.870",
      "purchasedBy": "Person1",
      "apples_qty": 1,
      "apples_amount": 100,
      "oranges_qty": 2,
      "oranges_amount": 150,
      "bananas_qty": 5,
      "bananas_amount": 200
    },
    {
      "orderID": 2,
      "purchaseDate": "2023-11-06T20:32:08.870",
      "purchasedBy": "Person2",
      "apples_qty": 2,
      "apples_amount": 200,
      "oranges_qty": null,
      "oranges_amount": null,
      "bananas_qty": 1,
      "bananas_amount": 40
    }
  ]

Note: I'm interested in the query logic and not schema suggestions.

Answer 1

You want to use dynamic column names. This is hardly possible without the use of dynamic SQL.

Also, You can create a View or use CTE to manually name columns.
Your idea of making the structure flat is questionable.
However, look at the example

with OrderItemsFlatten as(
  select p.orderId
    ,max(case when i.name='Apples' then qty end) as apples_qty
    ,max(case when i.name='Apples' then amount end) as apples_amount
    ,max(case when i.name='Oranges' then qty end) as oranges_qty
    ,max(case when i.name='Oranges' then amount end) as oranges_amount
    ,max(case when i.name='Bananas' then qty end) as bananas_qty
    ,max(case when i.name='Bananas' then amount end) as bananas_amount
  from Purchases p
  inner join Items i on i.itemId=p.itemId
  group by p.orderId
)
select     t.*,
    o.purchaseDate,
    o.purchasedBy
from Orders o
left join OrderItemsFlatten t on t.orderId=o.orderId
for json path,INCLUDE_NULL_VALUES

Result on your test data is

[
 {"orderId":1
  ,"apples_qty":1
  ,"apples_amount":100
  ,"oranges_qty":2
  ,"oranges_amount":150
  ,"bananas_qty":5
  ,"bananas_amount":200
  ,"purchaseDate":"2023-11-05T23:31:34.947"
  ,"purchasedBy":"Person1"
 }
,{
  "orderId":2,
  "apples_qty":2,
  "apples_amount":200,
  "oranges_qty":null,
  "oranges_amount":null,
  "bananas_qty":1,
  "bananas_amount":40,
  "purchaseDate":"2023-11-06T23:31:34.947",
  "purchasedBy":"Person2"
 }
,{
  "orderId":3,
  "apples_qty":null,
  "apples_amount":null,
  "oranges_qty":1,
  "oranges_amount":75,
  "bananas_qty":null,
  "bananas_amount":null,
  "purchaseDate":"2023-11-07T23:31:34.947",
  "purchasedBy":"Person3"
 }
]