laravel中如何从JSON字段中查询数据？

Question

{
    "管理人员": {
        "项目经理": {
            "1": {
                "user_id": "10",
                "memo": "111",
                "phone": "aaa"
            },
            "2": {
                "user_id": "11",
                "memo": "222",
                "phone": "bbb"
            },
            "3": {
                "user_id": "13",
                "memo": "333",
                "phone": "ccc"
            }
        }
    },
    "协助部门人员": {
        "设计部负责人": {
            "1": {
                "user_id": "3",
                "memo": "111",
                "phone": "aaa"
            },
            "2": {
                "user_id": "4",
                "memo": "222",
                "phone": "bbb"
            },
            "3": {
                "user_id": "5",
                "memo": "333",
                "phone": "ccc"
            }
        }
    }
}

如上的json格式，字段名是extra_attributes。
我想查询项目经理user_id为13的记录。
这个应该怎么实现呢？
用原生SQL怎么写，Laravel的又如何呢。

Answer 1

where('管理人员->项目经理->[*]->user_id',13)

如果需要原生SQL，可以输出 SQL ，然后就可以用了，

Answer 2

我试了一下，不知道是哪里的问题，这样写查不出来数据。麻烦再帮忙看一下吧。谢谢。

Project::where('extra_attributes->项目管理人员->施工员->[*]->user_id',22)->get();

上面的方法输出的SQL是

select
  *
from
  `projects`
where
  json_unquote(
    json_extract(
      `extra_attributes`,
      '$."项目管理人员"."施工员"."[*]"."user_id"'
    )
  ) = 22

数据库里的extra_attributes字段内容如下：

{
    "协助部门人员": {
        "设计部负责人": {
            "memo": null,
            "phone": null,
            "user_id": "82"
        },
        "采购部负责人": {
            "memo": null,
            "phone": null,
            "user_id": "99"
        },
        "预算部负责人": {
            "memo": null,
            "phone": null,
            "user_id": "91"
        }
    },
    "项目管理人员": {
        "劳资员": {
            "memo": null,
            "phone": null,
            "user_id": "27"
        },
        "施工员": {
            "1": {
                "memo": null,
                "phone": null,
                "user_id": "10"
            },
            "2": {
                "memo": null,
                "phone": null,
                "user_id": "22"
            },
            "3": {
                "memo": null,
                "phone": null,
                "user_id": "14"
            },
            "4": {
                "memo": null,
                "phone": null,
                "user_id": "55"
            }
        },
        "材料员": {
            "memo": null,
            "phone": null,
            "user_id": "53"
        },
        "项目经理": {
            "memo": null,
            "phone": null,
            "user_id": "6"
        },
        "项目负责人": {
            "memo": null,
            "phone": null,
            "user_id": "7"
        },
        "安全文明负责人": {
            "memo": null,
            "phone": null,
            "user_id": "31"
        },
        "技术负责人_投标": {
            "memo": null,
            "phone": null,
            "user_id": "6"
        },
        "技术负责人_现场": {
            "memo": null,
            "phone": null,
            "user_id": "20"
        }
    }
}

Answer 3

{
    "items": [
        {
            "city": "杭州市",
            "total": "26.80",
            "mileage": "12.6",
            "car_type": "快车",
            "time_geton": "03-1114:30周五",
            "station_geton": "浙江吉桂投资有限公司",
            "station_getoff": "杭州火车站"
        },
        {
            "city": "杭州市",
            "total": "16.10",
            "mileage": "10.1",
            "car_type": "快车",
            "time_geton": "03-2121:44周-",
            "station_geton": "浙江吉桂投资有限公司",
            "station_getoff": "金色蓝庭"
        }
        
    ],
    "common": {
        "date": "",
        "phone": "",
        "total": "100",
        "date_end": "",
        "date_start": ""
    }
}

请问我这种数据格式如果想搜索items里的city能这样写吗 ，字段名为ocrdata
ocrdata->items->[*]->city,我试过了好像不行