V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
v2exblog
V2EX  ›  程序员

MySQL5.7 的 JSON 数组,数组元素是 Object,想查询该数组 Object 的某几个字段同时成立!求各位帮帮孩子,查一下午不知道怎么解决

  •  
  •   v2exblog · 2021-10-15 18:31:40 +08:00 · 1596 次点击
    这是一个创建于 895 天前的主题,其中的信息可能已经有所发展或是发生改变。

    因为业务从 mongodb 迁移到 mysql5.7,有一些 mongo 特殊的查询就需要修改。
    想做到尽可能少改代码,并且需要支持 mongodb 的 $eleMatch 查询 (感觉难得不行)

    CREATE TABLE `Student` (
      `id` bigint AUTO_INCREMENT PRIMARY KEY, 
      `name` varchar(64),
      `gender` varchar(32),
      `data` json DEFAULT NULL
    ) 
    

    上面是简化后的表结构,实际上字段很丰富。主要是 data 字段是 JSON 格式,
    大概长这样:

    [
        {"date":"2021-01-01", "is_active": true, "tags":["A", "B", "C", "D"]},
        {"date":"2021-06-01", "is_active": false, "tags":["C", "D"]},
    ]
    

    想查询如下:

    1. 名字为 “小明”,性别为“女”,

    2. data.date 在 2020-08-01 到 2020-05-01 之间,

    3. 并且 is_active 为 true,

    4. tags 中含有"C" 和 "D"的 -- 这个可以通过读到内存后过滤

    查了一下午,试用了各种 json_extract 类似的函数,都没办法做到 [数组内的 Object 同时满足多个条件]

    网上还有说用 mysql8.0 自带的 json_table 函数,,目前还没有测试,不知道能不能见索引。

    求有经验的老哥说一下,跪谢跪谢跪谢跪谢跪谢跪谢跪谢

    第 1 条附言  ·  2021-10-15 21:10:59 +08:00
    调研了一圈还是不行,看来得和领导说完不成任务了:(
    9 条回复    2021-10-17 00:30:31 +08:00
    AlexaZhou
        1
    AlexaZhou  
       2021-10-15 18:44:21 +08:00
    现在遇到问题:
    没办法做到 [数组内的 Object 同时满足多个条件]

    那么用多个 json 方法,每个方法判断一个条件,再用 where 条件一 and 条件二 ... 连接不就可以?
    wuwukai007
        2
    wuwukai007  
       2021-10-15 18:51:16 +08:00
    json_contains + json_object
    v2exblog
        3
    v2exblog  
    OP
       2021-10-15 20:39:47 +08:00
    @AlexaZhou 不行的
    ```
    假如 data 的内容是这样的
    [{"sub_timeoff_date": "2020-12-31", "actual_exchange_date": ""}, {"sub_timeoff_date": "2021-01-01", "is_confirm_exchange": false}, {"sub_timeoff_date": "2021-06-01"}]
    ```

    那么这个 sql 查询就能匹配到
    ```

    SELECT * FROM User
    WHERE json_extract(data, '$[*].actual_exchange_date') = json_array('') and json_extract(data, '$[*].is_confirm_exchange') = json_array(false)
    limit 1

    ```
    moen
        4
    moen  
       2021-10-15 21:45:49 +08:00   ❤️ 1
    讲真,真要关系型数据库玩 json 为什么不用 PostgreSQL ?
    v2exblog
        5
    v2exblog  
    OP
       2021-10-16 10:00:53 +08:00
    @moen 要是俺自己的项目,别说 PG 了,我用 SQLITE 都行
    byaiu
        6
    byaiu  
       2021-10-16 11:25:27 +08:00 via iPhone
    建新表吧……
    v2exblog
        7
    v2exblog  
    OP
       2021-10-16 15:34:00 +08:00
    @wuwukai007 试过了确实有些边界情况没办法满足
    v2exblog
        8
    v2exblog  
    OP
       2021-10-16 15:34:19 +08:00
    @byaiu 看来只能新建了。。技术妥协
    swcat
        9
    swcat  
       2021-10-17 00:30:31 +08:00   ❤️ 1
    json_table 能做, 我做过, 性能不大好, 可能我用的方式不对
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1743 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 16:37 · PVG 00:37 · LAX 09:37 · JFK 12:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.