Hello! I have a table table in mysql and in it customer_id = 31 columns, custom_field , which stores the value JSON {"3":"Ivanovich","2":"nick_Ivan"} .

I need to select nickname nick_Ivan .

How can I write such a query?

 SELECT `custom_field` FROM `table` WHERE `customer_id` = 31;

Give: {"3":"Ivanovich","2":"nick_Ivan"}

Need to get nick_Ivan

  • one
    This is not done by SQL. If you already put json in the database (which you shouldn’t do when working with a database that doesn’t work with json), you get it entirely from the database as it is now and you already understand it in the language from which you refer to the database ... - Mike
  • @Mike I am writing in PHP, I put the OpenCart box in mysql, I know how to choose in PHP, I just thought it could be done by using the SQL query .... - ultimatum
  • one
    Judging by the docks SELECT JSON_EXTRACT (custom_field, '$ .2') ... dev.mysql.com/doc/refman/5.7/en/json.html - cache

2 answers 2

Use SUBSTRING_INDEX :

 select replace(replace(substring_index(custom_field, ':', -1), '"', ''), '}', '') from test

substring_index(custom_field, ':', -1) returns "nick_Ivan"} , from which it remains to remove the extra characters using replace .

An example on sqlfiddle .

It can be a little shorter, then as you prefer:

 select replace(substring_index(custom_field, ':"', -1), '"}', '') from test
  • The second option is generally fire! Thanks for the help! - ultimatum
  • one
    The second option is to fire as long as the JSON structure is unchanged. But it is worth adding there another field or changing the order ... - Akina
 mysql> set @x='{"3":"Ivanovich","2":"nick_Ivan"}'; Query OK, 0 rows affected (0.06 sec) mysql> select SUBSTRING_INDEX(SUBSTRING_INDEX(@x,'"2":"',-1),'"',1); +-------------------------------------------------------+ | SUBSTRING_INDEX(SUBSTRING_INDEX(@x,'"2":"',-1),'"',1) | +-------------------------------------------------------+ | nick_Ivan | +-------------------------------------------------------+ 1 row in set (0.06 sec)