Unquote JSON_EXTRACT value extracted from nested JSON object-CodePudding

I have a column with json type in MySQL database and the stored data structure is as follows:

table row 1:
    {
       "products":[
          {
             "price_c":"800",
             "item":"cloth"
          },
          {
             "price_f":"100",
             "item":"food"
          }
       ]
    }
table row 2:
    {
       "products":[
          {
             "price_c":"600",
             "item":"cloth"
          },
          {
             "price_f":"200",
             "item":"food"
          }
       ]
    }

I have been trying to extract the price of ass "item":"food" and did that with the query:

SELECT column->>'$**.price_f' as Price FROM table
where JSON_CONTAINS(column, '{"item":"food"}', '$.products');

But the problem with this approach is that it returns

 --------- 
|  Price  |
 --------- 
| ["100"] |
| ["200"] |
 ---------

Although I have used ->> but still the result is not unquoted and I have figured out that it is probably due to the wildcard $**.price. I have tried JSON_UNQUOTE and it also doesn't work. How should I unquote the outputs?

Here is the sample project in dbfiddle

CodePudding user response：

It is a string , so use string functions to remove the unwanted character

SELECT REGEXP_REPLACE(col1->>'$**.price_f' ,'[^0-9]','') as Price FROM tab1
where JSON_CONTAINS(col1, '{"item":"food"}', '$.products');

| Price |
| :---- |
| 200   |
| 100   |

db<>fiddle here

MySql 5.7 you need to preplace them one by one. and could be done in a function

SELECT REPLACE(REPLACE(REPLACE(col1->>'$**.price_f' ,'[',''),']',''),'"','')   as Price FROM tab1
where JSON_CONTAINS(col1, '{"item":"food"}', '$.products');

| Price |
| :---- |
| 200   |
| 100   |

db<>fiddle here