结合使用JSON_SEARCH和JSON_EXTRACT,我得到:“无效的JSON路径表达式". [英] Combining JSON_SEARCH and JSON_EXTRACT get me: "Invalid JSON path expression."
问题描述
我有一个名为广告系列"的表格.其中一列名为"filter_apps",其类型为 JSON
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"]
示例:
SELECT JSON_SEARCH(filter_apps, 'one', 'c130b917983c719495042e31306ffb') FROM campaigns;
结果:
"$[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;
结果
"c130b917983c719495042e31306ffb"
null
null
null
null
因此,在这一点上,我认为我可以使用查询JSON_EXTRACT提取值:
So in 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;
这导致我出现错误:
"[42000] [3143]无效的JSON路径表达式.错误在字符位置1附近."
"[42000][3143] Invalid JSON path expression. The error is around character position 1."
推荐答案
解决方案
就这么简单:
SELECT JSON_EXTRACT(filter_apps, JSON_UNQUOTE(JSON_SEARCH(filter_apps, 'one', 'c130b917983c719495042e31306ffb'))) FROM campaigns;
问题已解决!我将JSON_SEARCH包装在JSON_UNQUOTE方法中!
Problem resolved! I wrap JSON_SEARCH in a JSON_UNQUOTE method!
一个小技巧,我在这里找到了解决方案: https://dev.mysql.com/doc/refman/5.7/zh-CN/json-function-reference.html
A little tip, I found the solution here: https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
这篇关于结合使用JSON_SEARCH和JSON_EXTRACT,我得到:“无效的JSON路径表达式".的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!