Combining JSON_SEARCH and JSON_EXTRACT get me: "Invalid JSON path expression."

SOLUTION

Simple as that:

SELECT JSON_EXTRACT(filter_apps, JSON_UNQUOTE(JSON_SEARCH(filter_apps, 'one', 'c130b917983c719495042e31306ffb'))) FROM campaigns;

Problem resolved! I wrap JSON_SEARCH in a JSON_UNQUOTE method!

A little tip, I found the solution here: https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

It took me hours, as my JSON object is way more complex, but I found the solution for the 'all' option.

SELECT *,    REPLACE(REPLACE(LTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(filter_apps, ',', n), ',', -1)), '[', ''), ']', '') AS all_jsonFROM (    SELECT *, JSON_EXTRACT(filter_apps, JSON_UNQUOTE(JSON_SEARCH(filter_apps, 'all', 'c130b917983c719495042e31306ffb'))) AS hit    FROM campaigns) AS tJOIN (SELECT  @N := @N +1 AS n FROM campaigns, (SELECT @N:=0) dum LIMIT 10) numbers      ON CHAR_LENGTH(filter_apps) - CHAR_LENGTH(REPLACE(filter_apps, ',', '')) >= n - 1WHERE hit IS NOT NULL;    # for the "JOIN-FROM" use a table that has more or equal entries than the length of your longest JSON array# make sure the "JOIN-LIMIT" is higher or equal than the length of your longest JSON array

Query Explanation:

1. Inner SELECT:Main Select as asked in question with JSON_SEARCH Option 'all'
2. JOIN:a) SELECT table 'numbers':create a table which contains the numbers from 1 to user defined LIMIT.compare SQL SELECT to get the first N positive integersb) JOIN ON combined with Outer SELECT SUBSTRING_INDEX:splits the defined array column 'filter_apps' to the number of element of the array. Note user defined limit of 2)a) must be equal or greater than the longest array to split. compare SQL split values to multiple rows
3. REPLACE and LTRIM of Outer SELECT:used to remove remaining brackets and spaces of previous array
4. WHERE clause:to show only matching results of Inner SELECT