MYSQL Select into JSON_ARRAYAGG

Question

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!

please add the insert into for the result

nbk
– nbk

2023-10-12 14:41:22 +00:00
Commented Oct 12, 2023 at 14:41 — nbk
– nbk, Commented Oct 12, 2023 at 14:41

Bill Karwin · Accepted Answer · 2023-10-12 14:43:07Z

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

Wow thanks Bill!!! Never though about a UNION and really went down a rabbit hole with some weird attempts to make it work with using a join only. Thanks again for the great response!

Collectives™ on Stack Overflow

MYSQL Select into JSON_ARRAYAGG

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related