mysql中当字段为json并且为多维数组时候的查询

videos的表结构如下

id	mp4_list
1	[{"url":"fab37da2-0363-4431-a2b5-c0a1f9e0f8ac.mp4","startTimestamp":1552294988120,"endTimestamp":1552295321000,"durationMs":332880},{"url":"ed5994ba-33bf-45d2-a359-43d4f2eb4f33.mp4","startTimestamp":1552295388456,"endTimestamp":1552295419496,"durationMs":31040}]
2	[{"url": "e06efbf0-b202-11ea-a002-f3cb1985e0fd.mp4", "thumb": "/data/company/1/goods/9250/thumb/1592547672929.jpg", "vodId": 4886269, "deleted": "1", "fileSize": 22886481, "durationMs": null, "displayName": "20/06/19 14:21:12", "endTimestamp": 1592547775948, "startTimestamp": 1592547672929}, {"url": "e4f2d660-b202-11ea-a002-f3cb1985e0fd.mp4", "thumb": "1592548054525.jpg", "vodId": 4886978, "fileSize": 484001842, "durationMs": null, "displayName": "20/06/19 14:27:34", "endTimestamp": 1592550264253, "startTimestamp": 1592548054525}]
3	[{"url": "df7a7600-a6ce-11ea-932e-b9471fe243f2.mp4", "deleted": "1", "startTimestamp": 1560998717721}]

查找deleted为1的记录：

select id,mp4_list from videos where json_contains(json_array('1'),mp4_list->'$[*].deleted')