I have some tables that contains the payment details of students. In one of the tables, a column collects data in json string.
I want to extract the value of amount from the string below. However, you would notice that key 1 has amount
and key 2 has amount
too.
After extracting amount, I’m obviously getting the values for the two keys, but I want to extract only the amount value in key 2
{"1":{"amount":"500","date":"2023-01-06","amount_discount":"5500","amount_fine":"0","description":"","collected_by":"Super Admin(356)","payment_mode":"Cash","received_by":"1","inv_no":1},
"2":{"amount":"49500","date":"2023-01-22","amount_discount":"0","amount_fine":"0","description":"Being payment for tuition only","collected_by":"Juliet OLAJIDE(S20170181)","payment_mode":"bank_transfer","received_by":"32","inv_no":2}}
Below is my query:
SELECT student_fees_master.*,JSON_EXTRACT(student_fees_deposite.amount_detail, '$.*.amount') AS `deposit`
FROM `student_fees_master`
INNER JOIN fee_session_groups on fee_session_groups.id = student_fees_master.fee_session_group_id
INNER JOIN fee_groups_feetype on fee_groups_feetype.fee_session_group_id = fee_session_groups.id
INNER JOIN fee_groups on fee_groups.id=fee_groups_feetype.fee_groups_id
INNER JOIN feetype on feetype.id=fee_groups_feetype.feetype_id
LEFT JOIN student_fees_deposite on
student_fees_deposite.student_fees_master_id=student_fees_master.id
and student_fees_deposite.fee_groups_feetype_id=fee_groups_feetype.id
WHERE fee_groups_feetype.feetype_id=1
This is what I’m getting:
["500", "49500"]
But I want this:
["49500"]
How do I go about this?
2
Answers
You can do it as follows :
Demo here
A simple solution might be like this:
ORDER BY deposit DESC LIMIT 1