mysql sql -提取json字符串中的第二个键

efzxgjgh 于 2023-02-03 发布在 Mysql

关注(0)|答案(2)|浏览(236)

我有一些包含学生付款细节的表。在其中一个表中，一列收集json字符串中的数据。我想从下面的字符串中提取amount的值。但是，您会注意到key 1有amount，key 2也有amount。
提取amount之后，我显然得到了两个键的值，但是我只想提取键2中的amount值

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

下面是我的疑问：

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

这是我得到的["500", "49500"]
但我想要这个：["49500"]
我该怎么做？

mysql

来源：https://stackoverflow.com/questions/75328426/sql-extract-the-second-key-in-a-json-string

2条答案

按热度按时间

v1l68za41#

您可以按以下步骤操作：

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

赞(0）回复(0）举报 2023-02-03

jexiocij2#

一个简单的解决方案可能是这样的：
ORDER BY deposit DESC LIMIT 1

赞(0）回复(0）举报 2023-02-03