I want to retrieve SUM using key of JSON field in MySQL

Question

I want to get the summation of vehicle_rent, material_amount, other_amount, labour_amount, pa_amount and ta_amount from JSON type data field using MySQL json_extract and json_object or any other json related methods of MySQL.

{
    "task_vehicle": [
        {
            "vehicle_note": "Anonymous Proof",
            "vehicle_rent": "2000"
        }
    ],
    "task_material": [
        {
            "material_note": "Anonymous Proof",
            "material_amount": 200
        }
    ],
    "task_regular_amount": {
        "other": {
            "other_amount": "250"
        },
        "labour": {
            "labour_amount": "250"
        }
    },
    "task_purchase_breakdown": [
        {
            "pa_amount": "200"
        },
        {
            "pa_amount": "200"
        }
    ],
    "task_transport_breakdown": [
        {
            "ta_amount": "250",
            "transport_type": "Bus",
        }
    ]
}

I am able to extract and sum the values if json data is straight forward as follows-

{
"vehicle_rent": "2000",
"material_amount": "200",
"other_amount": "250".
"ta_amount": "250"
}

score 1 · Answer 1 · answered Mar 11 '21 at 20:49

The order can't be influneced.

SELECT JSON_OBJECT('vehicle_rent',JSON_EXTRACT('{
    "task_vehicle": [
        {
            "vehicle_note": "Anonymous Proof",
            "vehicle_rent": "2000"
        }
    ],
    "task_material": [
        {
            "material_note": "Anonymous Proof",
            "material_amount": 200
        }
    ],
    "task_regular_amount": {
        "other": {
            "other_amount": "250"
        },
        "labour": {
            "labour_amount": "250"
        }
    },
    "task_purchase_breakdown": [
        {
            "pa_amount": "200"
        },
        {
            "pa_amount": "200"
        }
    ],
    "task_transport_breakdown": [
        {
            "ta_amount": "250",
            "transport_type": "Bus"
        }
    ]
}'
, '$.task_vehicle[0].vehicle_rent'),'material_amount',
JSON_EXTRACT('{
    "task_vehicle": [
        {
            "vehicle_note": "Anonymous Proof",
            "vehicle_rent": "2000"
        }
    ],
    "task_material": [
        {
            "material_note": "Anonymous Proof",
            "material_amount": 200
        }
    ],
    "task_regular_amount": {
        "other": {
            "other_amount": "250"
        },
        "labour": {
            "labour_amount": "250"
        }
    },
    "task_purchase_breakdown": [
        {
            "pa_amount": "200"
        },
        {
            "pa_amount": "200"
        }
    ],
    "task_transport_breakdown": [
        {
            "ta_amount": "250",
            "transport_type": "Bus"
        }
    ]
}'
, '$.task_material[0].material_amount')
,'other_amount',
JSON_EXTRACT('{
    "task_vehicle": [
        {
            "vehicle_note": "Anonymous Proof",
            "vehicle_rent": "2000"
        }
    ],
    "task_material": [
        {
            "material_note": "Anonymous Proof",
            "material_amount": 200
        }
    ],
    "task_regular_amount": {
        "other": {
            "other_amount": "250"
        },
        "labour": {
            "labour_amount": "250"
        }
    },
    "task_purchase_breakdown": [
        {
            "pa_amount": "200"
        },
        {
            "pa_amount": "200"
        }
    ],
    "task_transport_breakdown": [
        {
            "ta_amount": "250",
            "transport_type": "Bus"
        }
    ]
}'
, '$.task_regular_amount.other.other_amount'),'ta_amount',
JSON_EXTRACT('{
    "task_vehicle": [
        {
            "vehicle_note": "Anonymous Proof",
            "vehicle_rent": "2000"
        }
    ],
    "task_material": [
        {
            "material_note": "Anonymous Proof",
            "material_amount": 200
        }
    ],
    "task_regular_amount": {
        "other": {
            "other_amount": "250"
        },
        "labour": {
            "labour_amount": "250"
        }
    },
    "task_purchase_breakdown": [
        {
            "pa_amount": "200"
        },
        {
            "pa_amount": "200"
        }
    ],
    "task_transport_breakdown": [
        {
            "ta_amount": "250",
            "transport_type": "Bus"
        }
    ]
}'
, '$.task_transport_breakdown[0].ta_amount')) as result

| result                                                                                      |
| :------------------------------------------------------------------------------------------ |
| {"ta_amount": "250", "other_amount": "250", "vehicle_rent": "2000", "material_amount": 200} |

db<>fiddle here

Thanks for your help though. It helps me a lot to understand the query and given way to think. Loved it <3. — Md. Khalakuzzaman Khan, Mar 21 '21 at 13:25

I want to retrieve SUM using key of JSON field in MySQL

1 Answers1