在JSON_EXTRACT或JSON_VALUE中使用查询结果

Question

嗨，我不知道这是可能的，但我有一些数据在MySQL在以下结构:

{"LinkId": "q1", "Answer": "Yes"},{"LinkId": "q2", "Answer": "Yes"},{"LinkId": "q3", "Answer": "No"} 

我可以使用JSON_SEARCH()来检索项目的位置(例如"Q3")

，例如:

Select JSON_SEARCH(data,'all','q3') FROM Table 

这将返回对象的位置-位置并不总是相同的，我得到的输出看起来像:

"$[3].path[32].LinkId" 
"$[4].path[2].LinkId" 

我可以做一个替换来改变LinkId的答案:

SELECT REPLACE(JSON_SEARCH(data,'all','q3' ),'LinkId','Answer')  AS Q_POS FROM TABLE

一切顺利到目前为止，我的"$[3].path[32].Answer如预期的那样

但是，如果我尝试在JSON_EXTRACT()中使用输出，我会得到一个无效的JSON路径表达式。错误是围绕字符位置1例如当在do

SELECT * ,JSON_EXTRACT(data,Q_POS) FROM
(SELECT * , REPLACE(JSON_SEARCH(data,'all','OOA' ),'LinkId','Answer')  AS Q_POS FROM Table ) AS RECORDS
WHERE Q_POS IS NOT NULL

谁知道是哪里出了问题?这可能吗?

Answer 1

事实证明，这似乎做到了:

SELECT * ,JSON_EXTRACT(data,Q_POS->>"$[0]") FROM
(SELECT * , REPLACE(JSON_SEARCH(data,'all','OOA' ),'LinkId','Answer')  AS Q_POS FROM Table ) AS RECORDS
WHERE Q_POS IS NOT NULL

或者只是JSON_UNQUOTE()

SELECT * ,JSON_EXTRACT(data,JSON_UNQUOTE(Q_POS)) FROM
(SELECT * , REPLACE(JSON_SEARCH(data,'all','OOA' ),'LinkId','Answer')  AS Q_POS FROM Table ) AS RECORDS
WHERE Q_POS IS NOT NULL