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

Question

I have a table names "campaigns". One of the columns is named "filter_apps" and his type is JSON

I have file rows and they just contain array of tokens like so:

["be3beb1fe916ee653ab825fd8fe022", "c130b917983c719495042e31306ffb"]
["4fef3f1999c78cf987960492da4d2a"]
["106c274e319bdeae8bcf8daf515b1f"]
["2521f0df6cffb7487d527319674cf3"]
["c130b917983c719495042e31306ffb"]

Examples:

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

Result:

"$[1]"
null
null
null
"$[0]"

Right now everything is correct, the matched columns come back. If I make a test I can prove it:

SELECT JSON_EXTRACT(filter_apps, '$[1]') FROM campaigns;

Result

"c130b917983c719495042e31306ffb"
null
null
null
null

So at this point I think I can extract the values using JSON_EXTRACT, my query:

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

That leads me to an error:

"[42000][3143] Invalid JSON path expression. The error is around character position 1."

Answer 1

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

Answer 2

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_json
FROM (
    SELECT *, JSON_EXTRACT(filter_apps, JSON_UNQUOTE(JSON_SEARCH(filter_apps, 'all', 'c130b917983c719495042e31306ffb'))) AS hit
    FROM campaigns
) AS t
JOIN (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 - 1
WHERE 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 integers b) 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