MySQL中循环使用JSON_EXTRACT()提取JSON内item对象数据

我需要在MySQL中使用JSON_EXTRACT()函数，处理JSON文档里items数组中的每个元素的item对象（注意item是单个对象而非数组），将提取到的tva_offer值以rateVAT_1、rateVAT_2这类带索引的命名形式返回。

JSON示例 #

{
    "quantity": 1,
    "amount": 10,
    "token": "27a6a39167bd6287f0c84e1534df232990e28d2d",
    "items": [
        {
            "quantity": "1",
            "amount": 10,
            "ht_amount": 9.090909090909092,
            "tva_amount": 0.9090909090909092,
            "ttc_amount": 10,
            "item": {
                "id": 108828,
                "user_id": 38141,
                "title": "Teste Offer",
                "description": "<p>lorem ipsum</p>",
                "type_offer": "offer",
                "brand": null,
                "amount": 25,
                "type_shipping": [
                    "hand"
                ],
                "amount_shipping": null,
                "tag_categories": [
                    4
                ],
                "geo_lat": "0",
                "geo_long": "0",
                "open_time": null,
                "is_flash_offer": 0,
                "is_activate": 1,
                "is_offer_moderate": 0,
                "is_boost": 0,
                "created_at": "2020-01-29 14:42:23",
                "updated_at": "2020-01-29 14:42:24",
                "slug": "teste-offer-108828",
                "site_web": null,
                "facebook": null,
                "instagram": null,
                "discount_amount": 10,
                "discount_percentage": "",
                "benefits": "",
                "type_price": "price",
                "type_purchase": "purchase_camcha",
                "website_offer": "",
                "views": null,
                "clicks": null,
                "favoris": null,
                "is_default_free_offer": 0,
                "is_camcha_offer": 0,
                "address": "Etoug, Cameroun",
                "postal_code": "0000",
                "tva_offer": 10,
                "created_date_format": "29\/01\/2020 02:42:23",
                "real_categories": [
                    {
                        "id": 4,
                        "parent_id": 0,
                        "name": "Services",
                        "slug": "services",
                        "image": null,
                        "description": null,
                        "position": 7,
                        "is_product_show": 0,
                        "created_at": "2019-12-12 15:24:11",
                        "updated_at": "2019-12-12 15:24:11",
                        "is_activate": 1,
                        "image_url": ""
                    }
                ],
                "offer_images": [],
                "user": {
                    "id": 38141,
                    "parent": 0,
                    "first_name": "yannick",
                    "last_name": "Holly",
                    "email": "fogang24@gmail.com",
                    "phone": "0698452884",
                    "address": "Etoug, Cameroun",
                    "address_2": null,
                    "postal_code": "0000",
                    "country_id": 0,
                    "city": "Paris",
                    "birthday": null,
                    "profile_image": null,
                    "email_verified_at": "2019-12-27 08:05:08",
                    "token": "",
                    "stripe_id": "cus_GSDlGFUn1qWrDU",
                    "role": "pro",
                    "web": null,
                    "facebook": null,
                    "instagram": null,
                    "is_active": 1,
                    "is_suspend": 0,
                    "is_pending": 0,
                    "is_verify": 1,
                    "is_admin_validate": 1,
                    "is_user_complete": 0,
                    "is_payment_active": 0,
                    "accept_cgu": 1,
                    "accept_year": 1,
                    "created_at": "2019-12-27 08:05:08",
                    "updated_at": "2019-12-30 08:25:58",
                    "payment_method_id": "pm_1FvJJdFfpClgVPUbz2wq9niK",
                    "slug": null,
                    "is_pro_advertiser": 0,
                    "url_profil_image": "",
                    "created_date_format": "27\/12\/2019 08:05:08",
                    "full_name": "Holly yannick",
                    "has_default_payment_method": false,
                    "user_company": {
                        "id": 53,
                        "user_id": 38141,
                        "company_status": null,
                        "siret": null,
                        "tva_number": null,
                        "code_naf": null,
                        "company_save_date": null,
                        "company_name": "",
                        "workplace": "",
                        "description": null,
                        "banniere": null,
                        "created_at": "2019-12-27 08:05:08",
                        "updated_at": "2019-12-27 08:05:08",
                        "slug": null,
                        "company_web": null,
                        "company_facebook": null,
                        "company_instagram": null,
                        "url_banniere": ""
                    }
                }
            }
        }
    ]
}

当前查询语句 #

SELECT....
user_companies.company_postal_code AS code_postal,
orders.id AS commande,
json_extract(orders.basket,'$.items[0].item[0].tva_offer') AS tauxTVA,
...

MySQL没有直接生成动态命名列的循环功能，可根据两种场景处理：

场景1：已知items数组最大长度 #

如果能确定items最多包含N个元素，可直接逐个提取并手动命名列：

SELECT
    user_companies.company_postal_code AS code_postal,
    orders.id AS commande,
    JSON_EXTRACT(orders.basket, '$.items[0].item.tva_offer') AS rateVAT_1,
    JSON_EXTRACT(orders.basket, '$.items[1].item.tva_offer') AS rateVAT_2,
    JSON_EXTRACT(orders.basket, '$.items[2].item.tva_offer') AS rateVAT_3
    -- 按需继续添加更多索引的列
FROM orders
JOIN user_companies ON ... -- 补充你的关联条件

注意：原查询中的$.items[0].item[0].tva_offer写法错误，item是单个对象而非数组，需去掉[0]，正确路径为$.items[0].item.tva_offer。

场景2：动态适配items数组长度（MySQL 8.0+） #

如果数组长度不固定，可先用JSON_TABLE将数组展开为行，再通过分组转换为带索引的列：

步骤1：展开数组为行 #

先提取每个items元素的tva_offer及其对应索引：

SELECT
    orders.id AS commande,
    user_companies.company_postal_code AS code_postal,
    jt.index + 1 AS item_index, -- 索引从1开始计数
    jt.tva_offer
FROM orders
JOIN user_companies ON ... -- 补充你的关联条件
JOIN JSON_TABLE(
    orders.basket,
    '$.items[*]' COLUMNS(
        index FOR ORDINALITY,
        tva_offer INT PATH '$.item.tva_offer'
    )
) jt;

步骤2：将行转换为带索引的列 #

通过子查询展开数据后，用MAX(CASE...)分组生成目标列：

SELECT
    commande,
    code_postal,
    MAX(CASE WHEN item_index = 1 THEN tva_offer END) AS rateVAT_1,
    MAX(CASE WHEN item_index = 2 THEN tva_offer END) AS rateVAT_2,
    MAX(CASE WHEN item_index = 3 THEN tva_offer END) AS rateVAT_3
    -- 按需添加更多CASE分支覆盖可能的元素数量
FROM (
    SELECT
        orders.id AS commande,
        user_companies.company_postal_code AS code_postal,
        jt.index + 1 AS item_index,
        jt.tva_offer
    FROM orders
    JOIN user_companies ON ... -- 补充你的关联条件
    JOIN JSON_TABLE(
        orders.basket,
        '$.items[*]' COLUMNS(
            index FOR ORDINALITY,
            tva_offer INT PATH '$.item.tva_offer'
        )
    ) jt
) t
GROUP BY commande, code_postal;

该方法可适配任意长度的items数组，只需添加足够的CASE分支覆盖最大可能的元素数即可。

内容的提问来源于stack exchange，提问作者athanar