sql -Extract the second key in a json string-CodePudding

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?

CodePudding user response：

You can do it as follows :

with cte as (
select '{"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}}' as deposit
)
select JSON_EXTRACT(deposit->"$.*", "$[1].amount") AS `deposit`
from cte;

Demo here

CodePudding user response：

A simple solution might be like this:

ORDER BY deposit DESC LIMIT 1