Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Supposed I have data below, select * from json_value_table :

id   json_value
1   {"name":"some value","price":50}    
2   {"name":"some value","price":100}   
3   {"name":"some value","price":150}   
4   {"name":"some value","price":250}   
I need the output as below：
id  name         pirce
1   some value   50
2   some value   100
3   some value   150
4   some value   250
JSON_EXTRACT() returns a JSON scalar (i.e. a double-quoted string like "some value"), which is technically a JSON document. If you want a plain string, also use JSON_UNQUOTE().
SELECT id, 
 JSON_UNQUOTE(JSON_EXTRACT(json_value,'$.name')) AS name, 
 JSON_UNQUOTE(JSON_EXTRACT(json_value,'$.price')) AS price 
FROM json_value_table;
MySQL has shortcut syntax for this:
SELECT id, 
 json_value->>'$.name' AS name, 
 json_value->>'$.price' AS price 
FROM json_value_table;
This is in the manual by the way: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-inline-path
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.