php - Mysql 使用 Like 运算符在 json 数组中搜索

Question

我对 MySQL json 类型有疑问。我想在 mysql json 数组中搜索并获取行。我的json数据是这个

{
    "id": 361,
    "email": "example@outlook.com",
    "code": null,
    "username": null,
    "permissions": null,
    "created_at": "2019-03-01 16:09",
    "updated_at": "2019-03-01 16:09",
    "user_profile": {
        "id": 361,
        "name": "Jhon",
        "surname": "Doe",
        "father_name": "NED",
        "birthday": "1994-12-15",
        "fin_code": "6A56BS7",
        "ssn": "AAA12830157",
        "account_number": "account123",
        "country": "USA",
        "city": "NEW YORK",
        "address": "EXample r",
        "address_n": "Khani/Bina",
        "mobile_phone": "(($717865643",
        "phone": "0123456789",
        "additional_email": "e.example@gmail.com",
        "education": [
            {
                "endDate": "2020-06",
                "startDate": "2015-09",
                "profession": "Computer Since",
                "university": "State University",
                "educationType": 99
            }
        ],
        "language": [
            {
                "id": 102,
                "level": 106
            },
            {
                "id": 103,
                "level": 106
            },
            {
                "id": 104,
                "level": 107
            }
        ],
        "computer_skills": [
            {
                "level": 106,
                "skill": "php"
            },
            {
                "level": 107,
                "skill": "java"
            }
        ],
        "family_composition": [
            {
                "name": "Jhon",
                "level": 126,
                "surname": "Snow",
                "birthday": "1992-02-08",
                "fatherName": "Ned"
            },
            {
                "name": "Jhon",
                "level": 126,
                "surname": "Snow",
                "birthday": "1992-05-18",
                "fatherName": "Ned"
            }
        ],
        "experience": [
            {
                "job": 128,
                "time": 22,
                "level": 8,
                "salary": 2200,
                "jobSign": 128,
                "jobStatus": 267,
                "startDate": "2012-12-12",
                "orderNumber": "123dsa",
                "jobSituation": 273,
                "additionalSalary": 800
            }
        ],
        "reproach": [
            {
                "doc": 228,
                "date": "2011-11-11",
                "note": "Some reason",
                "level": 225,
                "number": "123dsa",
                "reason": "islemir",
                "article": 233
            }
        ],
        "additional_information": "All is work",
        "citizen": {
            "id": 5,
            "name": "United States"
        },
        "cities": {
            "id": 21,
            "name": "New York"
        },
        "countries": {
            "id": 89,
            "name": "Unated States"
        },
        "gender": {
            "id": 1,
            "name": "Man"
        },
        "marital": {
            "id": 4,
            "name": "Single"
        },
        "work": {
            "id": 269,
            "name": "Able"
        },
        "party": {
            "id": 10,
            "name": "Digər"
        },
        "military": {
            "id": 121,
            "name": "OK"
        },
        "institution": null
    }
}

我想这样搜索:

WHERE education.'$[*].profession' Like %Computer%

但是这个语法不起作用。谢谢你的回复。如果这对任何建议有帮助，我在 Laravel 5.7 中开发了我的项目。我不使用 JSON_SEARCH()，因为此函数返回键，但我需要返回搜索查询的行。

Answer 1

如果你使用的是 MySQL 5.7，这应该可以工作

.....WHERE JSON_EXTRACT(education , "$.profession") Like '%Computer%';