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

videos的表结构如下

idmp4_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')

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值