8

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."

Jonathan Martins
  • 734
  • 7
  • 24

2 Answers2

14

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

Jonathan Martins
  • 734
  • 7
  • 24
  • Great question. I did the same here and worked like a charm. Unfortunately, I found this question only after already solved here by myself. On my case, additionally, I had to use a `REPLACE` function together to change last part of the returned "path" from `JSON_SEARCH`. – eddy85br Mar 10 '17 at 19:23
  • Thank you SO MUCH for this response. I had struggled with this for MONTHS and MONTHS and not found the solution to this. I even read in multiple places that using variables as the path expression was not permitted. This is a huge discovery that helps working with JSON in MySQL (particularly versions below MySQL 8). Thanks again! – tfrancois Jul 18 '19 at 15:40
  • 2
    Just curious what trick can be done to pass results from JSON_SEARCH to JSON_EXTRACT when `all` search mode instead of `one` is used. – Tom Raganowicz Feb 11 '20 at 08:19
  • thanks! a little counter intuitive (on MySQL's part); I'd just tried JSON_QUOTE before giving up and coming here. – Reece Feb 08 '21 at 22:11
  • Even I am curious to know what is the workaround if we use all instead of one. example: SELECT JSON_EXTRACT(filter_apps, JSON_UNQUOTE(JSON_SEARCH(filter_apps, 'all', 'c130b917983c719495042e31306ffb'))) FROM campaigns; – Kiran Thati Jul 22 '21 at 01:05
1

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
Jemima
  • 11
  • 2