How can I use JSON_EXTRACT in MySQL and get a string without the quotes?

Question

If I have a simple SELECT statement like this:

SELECT JSON_EXTRACT('{"username":"Alexander"}', '$.username');

I would expect it to return Alexander , but instead it returns "Alexander". How can I get rid of the quotes? Why does this function even return the quotes too?

Use JSON_UNQUOTE or column->>path (MySQL 5.7.13 and later): The ->> operator can be used wherever JSON_UNQUOTE(JSON_EXTRACT()) would be allowed.. — wchiquito
– wchiquito, Commented May 24, 2016 at 8:34

Paul Spiegel · Accepted Answer · 2019-08-10 19:45:04Z

96

You can use JSON_UNQUOTE to achieve this.

select JSON_UNQUOTE(JSON_EXTRACT(base, '$.scope')) as scope from t_name

ref: Functions That Modify JSON Values

edited Aug 10, 2019 at 19:45

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

answered Feb 7, 2018 at 2:59

Raul

9767 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ombre04 · Accepted Answer · 2017-01-18 11:33:25Z

-7

you can use replace() with it to remove quotation marks

SELECT replace(JSON_EXTRACT('{"username":"Alexander"}', '$.username'), '\"', '');

answered Jan 18, 2017 at 11:33

ombre04

473 bronze badges

1 Comment

A.D. Over a year ago

This is working only if there is no " in the value. This shouldn't be used. In json, " are encoded with \". JSON_EXTRACT returns directly the value without converting it to plain text. Not only you remove all " but you'll end up with extra \. Correct me if I am wrong!

Collectives™ on Stack Overflow

How can I use JSON_EXTRACT in MySQL and get a string without the quotes?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related