Question 20562 in Mysql Question posted in
The official MySQL documentation can be found here.

how to convert mysql json_object to string?

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
Tags:

2

Answers


  1. 0

    Try this:

    SELECT id, JSON_UNQUOTE(JSON_EXTRACT(json_value,'$.name')) AS name, JSON_UNQUOTE(JSON_EXTRACT(json_value,'$.price')) AS price FROM json_value_table;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search