SQL中如何提取JSON字符串内指定key（key2）的amount值？

问题背景 #

数据表student_fees_deposite的amount_detail列存储JSON格式字符串，示例如下：

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

原SQL使用JSON_EXTRACT(student_fees_deposite.amount_detail, '$.*.amount')提取所有子对象的amount，得到结果["500", "49500"]，但需求是仅获取key为"2"对应的amount值（格式保持数组：["49500"]）。

修改后的SQL语句 #

将JSON路径中的通配符*替换为具体的key名称"2"，如果需要保持数组格式，用JSON_ARRAY包裹提取结果：

SELECT student_fees_master.*,
       JSON_ARRAY(JSON_EXTRACT(student_fees_deposite.amount_detail, '$."2".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

关键说明 #

• 路径$."2".amount：精准定位到JSON中key为"2"的子对象下的amount字段，直接提取会得到单个值"49500"；
• 用JSON_ARRAY()包裹后，结果会保持数组格式["49500"]，和原查询的输出格式一致；
• 如果不需要数组格式，直接使用JSON_EXTRACT(student_fees_deposite.amount_detail, '$."2".amount')即可得到"49500"。

内容的提问来源于stack exchange，提问作者MissJargo