how to convert mysql json_object to string?

Question

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

Does this answer your question? [How to search JSON data in MySQL?](https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql) — Wahyu Kristianto, Mar 06 '23 at 06:50

Jordy · Answer 1 · 2023-03-06T07:20:07.117

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;

edited Mar 06 '23 at 07:20

answered Mar 06 '23 at 07:02

Jordy

1,802
2
6
25

Thanks so much, can I remove quotation mark of "some value"? – Mar 06 '23 at 07:05
No, because it will break the JSON format. – Jordy Mar 06 '23 at 07:09
User this, `JSON_UNQUOTE(json_extract(json_value,'$.name'))` – Mar 06 '23 at 07:17
I see, good way – Jordy Mar 06 '23 at 07:18

score 0 · Accepted Answer · answered Mar 06 '23 at 07:17

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

how to convert mysql json_object to string?

2 Answers2