CODEWITHSUNDEEP
mysqlmysql-json
Md. Khalakuzzaman Khan
Md. Khalakuzzaman Khan

Reputation: 194

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

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"
}

Upvotes: 0

Views: 66

Answers (1)

nbk
nbk

Reputation: 49385

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

Upvotes: 1

Related Questions