How to select exact JSON data from mysql database?-CodePudding

var sql = 'SELECT user_desc.language'  
'FROM user_desc '

Struggling with the Where operator, current thought process:

sql  = 'WHERE JSON_VALUE(user_desc.language, "$.english") = "true"'

This is obviously wrong (returns undefined), but you get the idea of what I'm trying to achieve.

Example how data is saved in database:

{"russian":"false","english":"true"}

CodePudding user response：

I think there are probably tons of different ways to do this.

You could try using JSON_SEARCH which is in MariaDB 10.2

The documentation here provides the full details, but here is an example:

Select
u.language
, Json_Unquote(Json_Extract(u.language, '$.english')) As json_language
From user_desc u
Where Json_Search(u.language, 'all', 'true') Like '%.english%';

You could then wrap this in a JavaScript string literal perhaps instead of concatenating as well.

Edit: Adding DB Fiddle using MariaDB 10.3 instead of MySQL 8.0 made by @Ergest-Basha with the quickness.