数据表中json的格式

{
  "goods": [
    {
      "type": 1,
      "number": 30,
      "goods_id": 1,
      "goods_name": "可靠(COCO)金装--成人纸尿裤L码/片",
      "goods_price": "4.70",
      "lease_price": 0
    },
    {
      "type": 1,
      "number": 20,
      "goods_id": 1,
      "goods_name": "可靠(COCO)金装--成人纸尿裤M码/片",
      "goods_price": "4.50",
      "lease_price": 0
    }
  ],
  "order_id": 7021,
  "order_sn": "CHMAIN20231211192454"
}

SQL

SELECT
    tile.goods_id AS '商品ID',
    MIN( tile.goods_name ) AS '商品名',
    MIN( tile.type ) AS '类型',
    SUM( tile.number ) AS '数量',
    SUM( tile.goods_price ) AS '购买合计',
    SUM( tile.lease_price ) AS '租赁合计' 
FROM
    `work_order`,
    JSON_TABLE (
        `extra`,
        "$.goods[*]" COLUMNS (
            `number` INT PATH "$.number",
            `goods_name` VARCHAR ( 255 ) PATH "$.goods_name",
            `goods_price` DECIMAL ( 10, 2 ) PATH '$.goods_price',
            `lease_price` DECIMAL ( 10, 2 ) PATH '$.lease_price',
            `type` INT PATH '$.type',
            `goods_id` INT PATH '$.goods_id'
        )) AS `tile`
GROUP BY
    tile.goods_id

查询结果

+--------+------------------------------------+------+--------+------------+-----------+
| 商品ID | 商品名                             | 类型 | 数量   | 购买合计   | 租赁合计  |
+--------+------------------------------------+------+--------+------------+-----------+
|     24 | 可靠（COCO）金装                    |    1 | 456  | 54.70    | 0.00      |
|      9 | 坐便椅                             |    1 | 45   | 4545.00  | 0.00      |
|      5 | 看护垫/三角垫                      |    1 | 45   | 678.00    | 0.00      |
+--------+------------------------------------+------+--------+------------+-----------+