CODEWITHSUNDEEP
mysqlmysql-jsonmariadb-10.3
rosia
rosia

Reputation: 229

Mariadb JSON_EXTRACT with condition on Json object

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'

Upvotes: 1

Views: 751

Answers (1)

Paul-Marie
Paul-Marie

Reputation: 1053

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');

Upvotes: 0

Related Questions