How to operate mysql's JSON?

Question

JSON field content of mysql

"[{"id":"1","name":"a"},{"id":"2","name":"b"},{"id":"3", "name":"c"}]"

How to query the data of name=b?
Note: Mine is a piece of data, a two-dimensional array converted to json

1. One way is to directly operate json
   Someone has provided select json_extract(json,'$[*].name' ) from table where json_extract(json,'$[* ].name')= "b" ;Method, no effect

2. Another method is to take it out, operate it, and put it back again.

The problem is that the extracted content is as shown in the picture. I have tried various methods but it cannot be operated.

Answer 1

Add another field for query operations. Try not to use the native functions of mysql.

Answer 2

The content of the json field should not have double quotes in the outermost layer, so it is not a JSON array.

In addition, it is best to provide complete text content for the content of the JSON field, and the content of the screenshot is incomplete.

Answer 3

json_extract(json,'$[*].name' )

What is returned is ["a", "b", "c"] will not match "b".

Hey, I won’t try to adjust it myself...