Mysql search in json array with Like operator

Question

I have a problem with MySQL json type. I want to search in mysql json array and get rows. My json data this

{
    "id": 361,
    "email": "[email protected]",
    "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": "[email protected]",
        "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
    }
}

I want to search like this:

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

But this syntax is not working. Thank you for replying. I developed my project in Laravel 5.7 if this is help for any suggestion. I don't use JSON_SEARCH() because this function returns the key but I need to return rows for my search query.

Answer 1

If you are on MySQL 5.7, this should work

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

Answer 2

I found this solution and it worked for me:

UPPER(education->"$[*].profession") LIKE UPPER("% Computer %")

For Laravel syntax I write the PHP code like this:

$query=$query->whereRaw('UPPER(education->"$[*].profession") LIKE UPPER("%' . $profession . '%")');