samedi 2 mars 2019

Mysql search in json array with Like operator

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": "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
        }
    }

and I want to search like this

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

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



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire