У меня есть данные json, как указано в user_details:
"[{"value":"sachin","label":"What your "first" name?"},{"value":"[email protected]","label":"What your email?"},{"value":"+911234567890","label":"What your "phone" number?"},{"value":"xyz","label":"What your city?"},{"value":"abc","label":"What your address?"}]"
Для этого я попытался с нижеследующим запросом, но он дает ошибку. Ошибка происходит ТОЛЬКО, когда мои данные содержат двойные кавычки ("). Как я могу сделать эту работу?
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;
Пытаться:
mysql> SET @'user_details' := '[
'> {"value":"sachin","label":"What\ your \\"first\\" name?"},
'> {"value":"[email protected]","label":"What\ your email?"},
'> {"value":"+911234567890","label":"What\ your \\"phone\\" number?"},
'> {"value":"xyz","label":"What\ your city?"},
'> {"value":"abc","label":"What\ 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)