MYSQL Select into JSON_ARRAYAGG

I’m trying to do something I thought would be simple but either I’m loosing it after trying to many different ways and my brain stopped working or this is not ‘simple’

I have 2 simple tables:

CREATE TABLE `Orders` (
  `Id` int NOT NULL,
  `Customer` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `Value` decimal(9,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `OrderItems` (
  `Id` int NOT NULL,
  `OrderId` int NOT NULL, // FK to Order (Id)
  `Qty` int NOT NULL,
  `Price` decimal(9,0) NOT NULL,
  `Total` decimal(9,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

I’m trying to select this in a JSON result that looks like this…

    {
        "Orders": [ 
            {
               "Id": 1,
               "Customer": "Joe Bloggs",
               "Value": 120.00,
               "Items": [
                   {
                        "Id": 1,
                        "Qty": 3,
                        "Price": 20.00,
                        "Total": 60.00
                   },
                   {
                        "Id": 2,
                        "Qty": 2,
                        "Price": 30.00,
                        "Total": 60.00
                   }
               ]
           },
           {
               "Id": 2,
               "Customer": "Sam Rockstar",
               "Value": 140.00,
               "Items": [
                   {
                        "Id": 1,
                        "Qty": 2,
                        "Price": 35.00,
                        "Total": 70.00
                   },
                   {
                        "Id": 2,
                        "Qty": 1,
                        "Price": 70.00,
                        "Total": 70.00
                   }
               ]
           },
           {
               "Id": 3,
               "Customer": "Jack Jones",
               "Value": 0.00,
               "Items": []
           }
        ]
    }

I don’t seem to be able to get the cases where the OrderItems is empty that it doesn’t show null information 🙁

Any pointers will be great – thanks!

>Solution :

Yes, it’s awkward that if you use LEFT OUTER JOIN OrderItems, and there are no matches, then there will still be columns for OrderItems, with NULLs in them. So you get an Items array with one element, but all the JSON fields are null.

The only way around this that I’ve found is to run two queries, one for an inner join, and the other to get an outer join for the orders with no orderitems. Then UNION them.

SELECT JSON_PRETTY(
  JSON_OBJECT(
    'Orders', JSON_ARRAYAGG(
      JSON_OBJECT(
        'Id', o.Id,
        'Customer', o.Customer,
        'Value', o.Value,
        'Items', o.Items
      )
    )
  )
) AS result
FROM (
  SELECT Orders.Id, Orders.Customer, Orders.Value,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'Id', OrderItems.Id,
        'Qty', OrderItems.Qty,
        'Price', OrderItems.Price,
        'Total', OrderItems.Total
      )
    ) AS Items
  FROM Orders JOIN OrderItems ON Orders.Id = OrderItems.OrderId
  GROUP BY Orders.Id
  UNION
  SELECT Orders.Id, Orders.Customer, Orders.Value,
    JSON_ARRAY() AS Items
  FROM Orders LEFT OUTER JOIN OrderItems ON Orders.Id = OrderItems.OrderId
  WHERE OrderItems.OrderId IS NULL
) AS o;

Result:

{
  "Orders": [
    {
      "Id": 1,
      "Items": [
        {
          "Id": 1,
          "Qty": 3,
          "Price": 20,
          "Total": 60.00
        },
        {
          "Id": 2,
          "Qty": 2,
          "Price": 30,
          "Total": 60.00
        }
      ],
      "Value": 120.00,
      "Customer": "Joe BLoggs"
    },
    {
      "Id": 2,
      "Items": [
        {
          "Id": 3,
          "Qty": 2,
          "Price": 35,
          "Total": 70.00
        },
        {
          "Id": 4,
          "Qty": 1,
          "Price": 70,
          "Total": 70.00
        }
      ],
      "Value": 140.00,
      "Customer": "Sam Rockstar"
    },
    {
      "Id": 3,
      "Items": [],
      "Value": 0.00,
      "Customer": "Jack Jones"
    }
  ]
} 

To get this to work for the dbfiddle I had to define primary keys in your tables.

Dbfiddle: https://dbfiddle.uk/BSJShx2x