JSON_CONTAINS string everywhere in the structure

Question

I have a JSON column in my MySQL database (5.7.25) that can contain any type of JSON structure (so I do not have any property that is common to all the rows)

How can I perform a query through laravel ELOQUENT in order to search rows that match a string in one of the property of the JSON column?

I tried with this:

JSON_CONTAINS(`payload`, "key")

But mySQL throws the following exception:

Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0

For example if the structure is similar to this:

{first_name:"John", "last_name":"Smith", "company":"Demo Company"}

I would like to search for all the rows that contain the word "Demo" in any of the properties (Also if the string is a substring of the json property value).

The only working way I found was the following:

JSON_SEARCH(payload, 'one', '%Demo%') IS NOT NULL

But it seems to be a bit slow. So I am asking if there's a faster way

Does https://stackoverflow.com/questions/33513625/invalid-json-text-in-argument-2-json-contains-in-mysql-5-7-8 help? — ceejayoz, Feb 04 '19 at 14:53

score 0 · Answer 1 · answered Nov 23 '21 at 23:14

The post title is different from the actual question, but for people who are wondering how to search inside anywhere in JSON, you could use simply a LIKE.

Given a json:

 {
    "rule": 4,
    "triggers": {
        "then": {
            "field": "Sessions_Remaining__c",
            ...
        },
        "when": ""
    }
}

You wont get any value by using:

SELECT * WHERE JSON_CONTAINS(json,'"Sessions_Remaining__c"')

So you can use instead:

SELECT * WHERE json LIKE '%"Sessions_Remaining__c"%';

JSON_CONTAINS string everywhere in the structure

1 Answers1