sql -Extract the second key in a json string

Question

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?

In this case you could probably do $."2".amount. That doesn't look for the second one, but uses the fact that the key is literally 2, so maybe worse (or better) than the answer provided. — EdmCoff
– EdmCoff, Commented Feb 2, 2023 at 20:31

SelVazi · Accepted Answer · 2023-02-02 20:35:47Z

1

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

edited Feb 2, 2023 at 20:35

answered Feb 2, 2023 at 20:30

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MissJargo Over a year ago

How would this work because I have a lot of data in the table. The json string I used as example is just one out of hundreds

SelVazi Over a year ago

instead of JSON_EXTRACT(student_fees_deposite.amount_detail, '$.*.amount') use JSON_EXTRACT(student_fees_deposite.amount_detail->"$.*", "$[1].amount")

MissJargo Over a year ago

yes, I like this better. Thanks

hhrzc · Accepted Answer · 2023-02-02 20:54:27Z

0

A simple solution might be like this:

ORDER BY deposit DESC LIMIT 1

answered Feb 2, 2023 at 20:54

hhrzc

2,0938 gold badges45 silver badges94 bronze badges

Collectives™ on Stack Overflow

sql -Extract the second key in a json string

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related