How can I remove string from json coulmn-CodePudding

I have 600 rows in a table called areas contains three things id, name, created_at name column has like this:

{"ar": "سفين", "en": "Safeen"}
{"ar": "شورش", "en": "Shorsh"}
.....

I want to keep just the name of ar and remove anything else:

{"ar": "سفين", "en": "Safeen"} => سفين

Is there any query to do such a thing?

My DB is MYSQL

CodePudding user response：

You can use JSON_ExTRAC

Also Bill Karmin mentioned to get rid of the Quotes around the text you can use JSON_UNQUOTE

And as he mentioned these function only are available since MySQL 5.7

CREATE TABLE mytable(tx  TEXT)

INSERT INTO mytable VALUES ('{"ar": "سفين", "en": "Safeen"}'),('{"ar": "شورش", "en": "Shorsh"}')

UPDATE mytable SET tx =  JSON_UNQUOTE(JSON_EXTRACT(tx, '$.ar'))

SELECT * FROM mytable

| tx       |
| :------- |
| سفين |
| شورش |

db<>fiddle here

CodePudding user response：

You can use

SELECT name.ar FROM areas

If you have defined name as a JSON data type