Mariadb JSON_EXTRACT with condition on Json object

Question

How do I extract only value based on "id" = "BAR"

+------------------------------+
| data                         | 
+------------------------------+
| {"id": "FOO", "code": "FOO"} | 
| {"id": "BAR", "code": "BAR"} |
+------------------------------+

desired output

+------------------------------+
| code                         | 
+------------------------------+  
| BAR                          |
+------------------------------+

I tried with this but it does not work.

 SELECT 
 JSON_EXTRACT(DATA, '$[*].code') as code
 FROM TABLETEST 
 where JSON_UNQUOTE(JSON_EXTRACT(data, '$[*].id')) ='BAR'

Answer 1

If you just want to get the data from the "code" column when the id = "BAR", the SQL you're looking for may simply be:

SELECT `code` FROM `data` WHERE id = "BAR"

Answer 2

The more straight forward solution is to use:

WHERE JSON_CONTAINS(JSON_EXTRACT(data, "$[*].id"), 'BAR');

Like:

SELECT 
  JSON_EXTRACT(DATA, '$[*].code') as code
FROM TABLETEST 
  WHERE JSON_CONTAINS(JSON_EXTRACT(data, "$[*].id"), 'BAR');