How to search through unknown property name in database with Laravel

Question

I'm trying to search database with json contains method of laravel. Here is my JSON of one database line:

{
    "row": {
        "1": {
            "ID":"110555175667"
        },
        "2": {
            "ID":"11023235667"
        },
        "3": {
            "ID":"11001414141667"
        },
        "4": {
            "ID":"11023235667"
        },
        "5": {
            "ID":"1100012222225667"
        },
    }
}

I want to search ID, but as you see there are numbers as properties.

In example I want to find 11023235667. I've tried it like that:

->whereJsonContains('json', [['row' => ['1' => ['ID' => '11023235667']]]])

But it didn't worked. How can I do it?

EDIT:

I have also tried this:

->whereRaw('JSON_CONTAINS(json, "$.row.*.ID", "11023235667")')

I know the property of row must be JSON array to accomplish to match the id, but it has been set as JSON object

Answer 1

The usage of JSON_CONTAINS() accepts a JSON document as its second argument, not a path.

You could use that path to extract the ID's into an array:

SELECT JSON_EXTRACT(json, '$.row.*.ID') FROM ...
+--------------------------------------------------------------------------------------+
| ["110555175667", "11023235667", "11001414141667", "11023235667", "1100012222225667"] |
+--------------------------------------------------------------------------------------+

Using this, you can search the resulting array:

SELECT ... FROM mytable 
WHERE JSON_SEARCH(JSON_EXTRACT(json, '$.row.*.ID'), 'one', '11023235667') IS NOT NULL;

You would need to do this using whereRaw() in Laravel, because Laravel doesn't have a builtin query builder function for this expression.

Tip: As soon as you reference a JSON column in the WHERE clause of an SQL query, your query becomes harder to write, and harder to optimize. This should be a red flag indicating your design is wrong. You would be better off storing data in normal rows and columns, not JSON.