How can I select all possible JSON Data in arrow syntax/JSON Extract in SQL

Question

I need to be able to access all the available JSON data, the problem is that a lot of it is nested.

I currently have this query.

SELECT * FROM `system_log` WHERE entry->"$[0]" LIKE "%search_term%";

I need instead of entry->"$[0]", something like entry->"$*"

I think the arrow syntax is short for JSON_EXTRACT which I think would mean that a solution for extract would work for the arrow syntax.

{
    " Name": {
        "after": "Shop",
        "before": "Supermarket"
    }
}

This is an example of my JSON data and as you can see there are multiple levels to it meaning that entry->"$[0]" won't catch it.

version 8.0.19 of SQL

What I've tried so far is entry->"$[0]" and then prepending [0] after, but this solution does not seem very dynamic as the JSON data could get deeper and deeper.

Answer 1

JSON_SEARCH() won't work for the search you describe, because JSON_SEARCH() only searches for full string matches, not wildcards.

If you truly cannot predict the structure of your JSON, and you just want to find if the pattern '%search_term%' appears anywhere, then just treat the whole JSON document as a string, and use LIKE:

SELECT * FROM `system_log` WHERE entry LIKE "%search_term%";

If you have more specific search requirements, then you'll have to come up with a way to predict the path to the value you're searching for in your JSON document. That's something I cannot help you with, because I don't know your usage of JSON.