1

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'
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
rosia
  • 219
  • 1
  • 2
  • 6

2 Answers2

0

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');
Paul-Marie
  • 874
  • 1
  • 6
  • 24
-2

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"
Nick Sabia
  • 129
  • 8
  • It is a json object like [ {"id": "FOO", "code": "hello"} , {"id": "BAR", "code": "bye"} ] – rosia Nov 24 '20 at 17:54
  • I'm a bit confused by this part: `where JSON_UNQUOTE(JSON_EXTRACT(data, '$[*].id')) = 'BAR'` Generally when you use the WHERE keyword in SQL, it goes like this: `WHERE [column] = [value]` With that said, you may be able to achieve the desired result by doing this - `SELECT JSON_EXTRACT(DATA, '$[*].code') as code FROM TABLETEST where id = 'BAR'` – Nick Sabia Nov 24 '20 at 18:08