How to search through unknown property name in database with Laravel

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

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.