CODEWITHSUNDEEP
mysqljson
Willem van Gerven
Willem van Gerven

Reputation: 1597

Extract value without quotation mark from MySQL JSON data type

I have started using the JSON data type in mysql 5.7. Is there a way to extract a value without the quotation marks? For instance when setting up a virtual index.

Example: 

mysql> INSERT INTO test (data) VALUES ('{"type": "user" , 
"content" : { "username": "jdoe", "firstname" : "John", "lastname" : "Doe" } }');

mysql> SELECT json_extract(data,'$.type') FROM test;
+-----------------------------+
| json_extract(data,'$.type') |
+-----------------------------+
| "user"                      |
+-----------------------------+

How to get

+-----------------------------+
| json_extract(data,'$.type') |
+-----------------------------+
| user                        |
+-----------------------------+

?

Upvotes: 88

Views: 71875

Answers (9)

Liam
Liam

Reputation: 5506

I stumbled across this when I had a string stored in the database that was a json object - this is how you can extract the whole raw object into another object:

set @json = '{"a":1}';
select json_object('object', json_extract(@json, '$'));

This returns

{"object": {"a": 1}}

Upvotes: 0

MohaMad
MohaMad

Reputation: 2855

You can use ->> operator to extract unquoted data, simply!

SELECT JSONCOL->>'$.PATH' FROM tableName

Two other ways:

  • JSON_UNQUOTE(JSON_EXTRACT(column, path))
  • JSON_UNQUOTE(column->path)

Note: Three different ways yield to the same command, as EXPLAIN explains:

As with ->, the ->> operator is always expanded in the output of EXPLAIN, as the following example demonstrates:

EXPLAIN SELECT c->>'$.name' AS name FROM jemp WHERE g > 2 ;
SHOW WARNINGS ;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select
json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from
`jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

read more on MySQL Reference Manual https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#operator_json-inline-path

Note: The ->> operator was added in MySQL 5.7.13

Upvotes: 178

halillusion
halillusion

Reputation: 338

A different method is;

SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.type')) FROM test

Upvotes: 11

Lukasz Szozda
Lukasz Szozda

Reputation: 176264

MySQL 8.0.21 supports JSON_VALUE function

Extracts a value from a JSON document at the path given in the specified document, and returns the extracted value, optionally converting it to a desired type. The complete syntax is shown here:

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])
on_empty:
       {NULL | ERROR | DEFAULT value} ON EMPTY
on_error:
       {NULL | ERROR | DEFAULT value} ON ERROR

If not specified by a RETURNING clause, the JSON_VALUE() function's return type is VARCHAR(512)

db<>fiddle demo

SELECT json_value(data,'$.type')
FROM test;
-- user

Upvotes: 11

ivan88
ivan88

Reputation: 868

You can use JSON_UNQUOTE() method:

SELECT JSON_UNQUOTE(json_extract(data,'$.type')) FROM test;

This method will deal with internal quotes, for instance:

SET @t1 := '{"a": "Hello \\\"Name\\\""}';
SET @j := CAST(@t1 AS JSON);
SET @tOut := JSON_EXTRACT(@j, '$.a');
SELECT @t1, @j, @tOut, JSON_UNQUOTE(@tOut), TRIM(BOTH '"' FROM @tOut);

will give:

@t1     : {"a": "Hello \"Name\""}
@j      : {"a": "Hello \"Name\""}
@tOut   : "Hello \"Name\""
unquote : Hello "Name"
trim    : Hello \"Name\

I believe that the unquote is better in almost all circumstances.

Upvotes: 76

Steve Lloyd
Steve Lloyd

Reputation: 949

You can also modify the column itself so that the quotes are not in the generated column

alter table your_table add your_field varchar(25) GENERATED ALWAYS AS (TRIM(BOTH '"' FROM json_extract(json_field,'$.your_field')))

Upvotes: -3

Willem van Gerven
Willem van Gerven

Reputation: 1597

I have found a solution that is most clean. CAST function didn't work, and @Pryanshu's answer can be made independent from the value length by using

SELECT TRIM(BOTH '"' FROM json_extract(data,'$.type')) FROM test;

Upvotes: -6

Nir Levy
Nir Levy

Reputation: 12953

you can use CAST() function to convert from json object to varchar

SELECT CAST(json_extract(data,'$.type') AS VARCHAR) FROM test;

Upvotes: 0

Priyanshu
Priyanshu

Reputation: 881

SELECT left(right(json_extract(data,'$.type'),5),4) FROM test;

Upvotes: -4

Related Questions