I am trying to perform a search inside a table containing JSON objects, but I can't figure out how to make it work with CONCAT_WS in order to search by multiple words at once, in this case, the combination of first_name & last name.

The query is working well when the search is made with a single word, (first_name, last_name, email, or phone) but does not return anything when both first_name and last_name are sent into the backend.

Model::whereRaw('
        JSON_EXTRACT(data, "$.start") >= ?
            AND
        JSON_EXTRACT(data, "$.personal") = ?
            AND 
        (
            LOWER(JSON_EXTRACT(data, "$.user.first_name")) LIKE ?
                OR
            LOWER(JSON_EXTRACT(data, "$.user.last_name")) LIKE ?
                OR
            LOWER(JSON_EXTRACT(data, "$.user.email")) LIKE ?
                OR
            LOWER(JSON_EXTRACT(data, "$.user.phone")) LIKE ?
                OR
            CONCAT_WS(" ",LOWER(JSON_EXTRACT(data, "$.user.first_name")),LOWER(JSON_EXTRACT(data, "$.user.last_name"))) LIKE ?
        )
        ', [
            Carbon::now(),
            0,
            '"%' . trim(strtolower($request->searchQuery)) . '%"',
            '"%' . trim(strtolower($request->searchQuery)) . '%"',
            '"%' . trim(strtolower($request->searchQuery)) . '%"',
            '"%' . trim(strtolower($request->searchQuery)) . '%"',
            '"%' . trim(strtolower($request->searchQuery)) . '%"',
        ])
        ->orderByRaw('JSON_EXTRACT(data, "$.start") ASC')
        ->selectRaw('table.*, true AS future')
        ->get();

Data structure:

[
    {
        "id": 1,
        "data": {
            "id": 1,
            "user": {
                "id": 1,
                "email": "[email protected]",
                "phone": "(000) 000-0000",
                "last_name": "John",
                "first_name": "Snow",
            },
            "start": "2022-03-24 17:00:00",
            "personal": 0
        }
    },
    {
        "id": 2,
        "data": {
            "id": 2,
            "user": {
                "id": 2,
                "email": "[email protected]",
                "phone": "(000) 000-0000",
                "last_name": "Some",
                "first_name": "Name",
            },
            "start": "2022-03-24 17:00:00",
            "personal": 0
        }
    },
    {
        "id": 3,
        "data": {
            "id": 3,
            "user": {
                "id": 3,
                "email": "[email protected]",
                "phone": "(000) 000-0000",
                "last_name": "Other",
                "first_name": "Name",
            },
            "start": "2022-03-24 17:00:00",
            "personal": 0
        }
    }
]

CodePudding user response：

you need to edit

• replace CONCAT_WS() with CONCAT()
• use JSON_UNQUOTE() at JSON_EXTRACT() result before concating

so this line
CONCAT_WS(" ",LOWER(JSON_EXTRACT(data, "$.user.first_name")),LOWER(JSON_EXTRACT(data, "$.user.last_name")))

need to changes to this

CONCAT(
  LOWER(JSON_UNQUOTE(JSON_EXTRACT(data,"$.user.first_name"))),
  LOWER(JSON_UNQUOTE(JSON_EXTRACT(data, "$.user.last_name")))
)

justification the problem was caused due to

• the use of the trim() method, which deletes the white spaces between the words, while you concating firstname and lastname with spaces
• also JSON_EXTRACT() returns the values with double quotes which will not cause an issue when you use like on one word, but when two words are added the quotes will exist in the middle,and the condition will be false so I used JSON_UNQUOTE to trim it the quotes

so you will end up with the following query

SELECT * FROM `table_name` WHERE JSON_EXTRACT(data, "$.start") >= '2022-03-24 03:09:08'
            AND
        JSON_EXTRACT(data, "$.personal") = 0
            AND 
        (
            LOWER(JSON_EXTRACT(data, "$.user.first_name")) LIKE "%snowjohn%"
                OR
            LOWER(JSON_EXTRACT(data, "$.user.last_name")) LIKE  "%snowjohn%"
                OR
            LOWER(JSON_EXTRACT(data, "$.user.email")) LIKE  "%snowjohn%"
                OR
            LOWER(JSON_EXTRACT(data, "$.user.phone")) LIKE  "%snowjohn%"
                OR
            CONCAT(LOWER(JSON_UNQUOTE(JSON_EXTRACT(data, "$.user.first_name"))),LOWER(JSON_UNQUOTE(JSON_EXTRACT(data, "$.user.last_name"))))  LIKE  "%snowjohn%"
        );

CodePudding user response：

So, with the help from Omar, this is the fix for my issue!

Model::whereRaw('
        JSON_EXTRACT(data, "$.start") >= ?
            AND
        JSON_EXTRACT(data, "$.personal") = ?
            AND 
        (
            LOWER(JSON_EXTRACT(data, "$.user.first_name")) LIKE ?
                OR
            LOWER(JSON_EXTRACT(data, "$.user.last_name")) LIKE ?
                OR
            LOWER(JSON_EXTRACT(data, "$.user.email")) LIKE ?
                OR
            LOWER(JSON_EXTRACT(data, "$.user.phone")) LIKE ?
                OR
            CONCAT_WS(" ",
                LOWER(
                    JSON_UNQUOTE(
                        JSON_EXTRACT(data, "$.user.first_name")
                    )
                ),
                LOWER(
                    JSON_UNQUOTE(
                        JSON_EXTRACT(data, "$.user.last_name")
                    )
                )
            ) LIKE ?
        )
        ', [
            Carbon::now(),
            0,
            '"%' . trim(strtolower($request->searchQuery)) . '%"',
            '"%' . trim(strtolower($request->searchQuery)) . '%"',
            '"%' . trim(strtolower($request->searchQuery)) . '%"',
            '"%' . trim(strtolower($request->searchQuery)) . '%"',
            '%'  . trim(strtolower($request->searchQuery)) . '%',
        ])
        ->orderByRaw('JSON_EXTRACT(data, "$.start") ASC')
        ->selectRaw('table.*, true AS future')
        ->get();