SELECT id from user_leave_hour WHERE user_id = '61' and leave_type_id = '7' and JSON_EXTRACT(data, '$**.aid') = "120221031001";
如上述query我想在where條件中使用json中的aid值當條件，想請前輩們我的作法問題出錯在哪，謝謝!
以下是json data :
{"child1":[{"leave_hour":"88.0","start_date":"2022-12-01","end_date":"2024-11-30","aid":"120221031001"},{"leave_hour":"88.0","start_date":"2022-12-16","end_date":"2024-12-15","aid":"120221031002"}],"child2":[{"leave_hour":"80.0","start_date":"2023-01-02","end_date":"2025-01-01","aid":"120221031003"}]}
已邀請的邦友 {{ invite_list.length }}/5
json data裡的child1跟child2 是用陣列去包資料，所以取值的時候，需要用陣列去取
如下：
SELECT id from user_leave_hour WHERE user_id = '61' and leave_type_id = '7' and JSON_EXTRACT(data, '$.child1[0].aid') = "120221031001";
iThome鐵人賽
看影片追技術