结合JSON_SEARCH和JSON_EXTRACT得到:“无效的JSON路径表达式。”

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

我有一个表名“活动”。其中一列名为“filter_apps”，其类型为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 in this point I think I can extract the values using JSON_EXTRACT, my query:

因此，在这一点上，我认为我可以使用JSON_EXTRACT提取值，我的查询:

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

"[42000][3143]无效的JSON路径表达式。错误在字符位置1附近。

1 个解决方案

#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!

问题解决!我用JSON_UNQUOTE方法封装JSON_SEARCH !

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

我在这里找到了解决方案:https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

#1