CODEWITHSUNDEEP
mysqljson
Sriram Arvind Lakshmanakumar
Sriram Arvind Lakshmanakumar

Reputation: 676

Unable to Extract Keys have special Characters in MySQL using JSON_EXTRACT

i have a json field in my table(MySQL Database).

the following is the structure: 

{
    "article":{
            "Key's 1":{
                "value":"24"
            }
            "Key's of the something's 2":{
                "value":"55"
            }
    }

}

i am trying to extract the "value" field of "Key's 1".

Due special character such as single quote and space, i am unable use JSON_EXTRACT function.

It give the error: Invalid JSON path expression.This error is around character position no : 10

My Query: 

select 
    JSON_EXTRACT(analytics_json,'$.article.Key\'s 1.value')As value
from
    tbl_json_data;

Even after place a backslash, i am getting the error.

Upvotes: 5

Views: 3237

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521514

You may escape each JSON path component in double quotes to handle special characters as well as spaces. The following works:

SELECT 
    JSON_EXTRACT(analytics_json,'$.article."Key''s 1".value') AS value
FROM tbl_json_data;

Demo

Note that your key name actually has two problems. First, it contains a literal single quote. We can handle that by just doubling up two single quotes. The key name also contains whitespace. By escaping in double quotes we may workaround this problem, but it would probably be best to avoid using JSON keys which have whitespace.

Upvotes: 7

Related Questions