I try to use the JSON_EXTRACT() function, on my request I have to loop on the "item" object (attention not "items" which is present several times in the JSON file) :
{
"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": ""
}
}
}
}
]
}
This my request :
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,
...
I would like to know if it is possible to loop over the item attribute several times and retrieve the value as an indexed name, for example: rateVAT_1, rateVAT_2, ....
Thank you for your help. Cédric.
I tried this doc, but it doesn't say anything about curls
https://database.guide/json_extract-return-data-from-a-json-document-in-mysql/
