CODEWITHSUNDEEP
mysqljson
dang
dang

Reputation: 2412

MySQL JSON_SEARCH - not working with double quotes

I have json data as follow in user_details:

"[{"value":"sachin","label":"What's your "first" name?"},{"value":"[email protected]","label":"What's your email?"},{"value":"+911234567890","label":"What's your "phone" number?"},{"value":"xyz","label":"What's your city?"},{"value":"abc","label":"What's your address?"}]"

For this I tried with the below query, but it is giving the error. The error happens ONLY when my data contains double quotes ("). How can I make this work?

SELECT CASE WHEN json_search(user_details, 'one', '%name%', null, '$[*].label')
IS NOT NULL THEN 'name'
WHEN json_search(user_details, 'one', '%email%', null, '$[*].label')
IS NOT NULL THEN 'email'
WHEN json_search(user_details, 'one', '%phone number%', null, '$[*].label') IS NOT NULL THEN 'phone'
ELSE 'id' END type,
CASE WHEN json_search(user_details, 'one', '%name%', null, '$[*].label')
IS NOT NULL THEN
json_unquote(json_extract(user_details, concat(json_unquote(replace(json_search(user_details, 'one', '%name%', null,
'$[*].label'),'.label', '')),'.value')))
WHEN json_search(user_details, 'one', '%email%', null, '$[*].label') IS NOT NULL THEN
json_unquote(json_extract(user_details, concat(json_unquote(replace(json_search(user_details, 'one', '%email%', null,
'$[*].label'),'.label', '')),'.value')))
WHEN json_search(user_details, 'one', '%phone number%', null, '$[*].label') IS NOT NULL THEN
json_unquote(json_extract(user_details,concat(json_unquote(replace(json_search(user_details, 'one', '%phone%', null,
'$[*].label'),'.label', '')),'.value')))ELSE user_id END value FROM json_user;

Upvotes: 0

Views: 685

Answers (1)

wchiquito
wchiquito

Reputation: 16551

Try:

mysql> SET @`user_details` := '[
    '>   {"value":"sachin","label":"What\'s your \\"first\\" name?"},
    '>   {"value":"[email protected]","label":"What\'s your email?"},
    '>   {"value":"+911234567890","label":"What\'s your \\"phone\\" number?"},
    '>   {"value":"xyz","label":"What\'s your city?"},
    '>   {"value":"abc","label":"What\'s your address?"}
    '> ]';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_VALID(@`user_details`);
+-----------------------------+
| JSON_VALID(@`user_details`) |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    -> JSON_UNQUOTE(
    ->   JSON_EXTRACT(
    ->     @`user_details`,
    ->     JSON_UNQUOTE(
    ->       REPLACE(
    ->         JSON_SEARCH(
    ->           @`user_details`,
    ->           'one',
    ->           '%phone%',
    ->           null,
    ->           '$[*].label'
    ->         ),
    ->         '.label',
    ->         '.value'
    ->       )
    ->     )
    ->   )
    -> ) `phone`;
+---------------+
| phone         |
+---------------+
| +911234567890 |
+---------------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions