use the results of a Query in JSON_EXTRACT or JSON_VALUE

Question

Hi I don't know if this is possible, but I have some data in MySQL in the following structure :

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

I can use JSON_SEARCH() to Retrieve the Position of an item (e.g. "Q3")

with something like :

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

This will return the Position of the Object - the position isn't always the same, I get an output that looks like :

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

I can do a replace to change the LinkId to the Answer :

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

All Good So far gives me "$[3].path[32].Answer as expected

However, if I try and use the Output in a JSON_EXTRACT() I get a invalid JSON path expression. Error is around Character position 1 e.g. when in 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

Anyone have any ideas on what's going wrong? Is this even possible ?

Answer 1

Turns out that this seems to do the Trick :

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

Or just 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