How to Use Json_Extract in MySQL and Get a String Without the Quotes

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

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

MySQL: json extract remove quotes

I guess that the error is related to the connector driver. I'm using mysql-connector-java-5.1.45 with SQL Developer. Still haven't found a resolution (tryed also another version) but doing the replace directly on the mysql server it works.

Extract value by key with double quote and backslash in json column

Here's a test:

mysql> select json_extract(cast('{"simple": "val1", "h\\"simple": "val2"}' as json),
    '$."h\\"simple"') as h_simple;
+----------+
| h_simple |
+----------+
| "val2"   |
+----------+

You need a literal \ in the JSON path expression so it escapes the " to JSON. But MySQL strings treat backslash as special, so you have to use a double-backslash to ensure that a single literal backslash makes it past MySQL's string literal parser.

Update and replace unquoted JSON strings

You may use the next procedure:

CREATE PROCEDURE quote_value(max_amount INT)
BEGIN
REPEAT
    UPDATE test
    SET settings = JSON_REPLACE(settings, CONCAT('$[', max_amount, '].value'), CAST(JSON_UNQUOTE(JSON_EXTRACT(settings, CONCAT('$[', max_amount, '].value'))) AS CHAR));
    SET max_amount = max_amount - 1;
UNTIL max_amount < 0 END REPEAT;
END

max_amount parameter defines the amount of objects in the array to be updated (do not forget that the array elements are counted from zero). So set it to max objects per array amount value.

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=166f43d44e57b62da034bd9530713beb

Extract value from mysql json string stored as varchar

None of the answers helped resolve. So what I did was exported the table to csv, loaded to google sheet ( after splitting it into manageable size) and than used 'text to column' to extract the value from the json-like string.

After that, imported the csv into a temp table, tested and brought back live.

MySQL LIKE with json_extract

Okay, I was able to solve the case insensitivity by adding COLLATE utf8mb4_general_ci after the LIKE clause.

So the point here is to find a working collation, which in its turn can be found by researching the db you work with.

---

Related Topics