ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Из JSON извлечь значения
6 сообщений из 6, страница 1 из 1
Из JSON извлечь значения
    #40101687
cupko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
cupko
Гость
Глубоких познаний в sql нет, может кто-то поможет составить запрос для извления данных из JSON?

Есть таблица, с колонками id и comment. В колонке comment данные хранятся в виде JSON. Есть 2 вида JSON записей для данных, в которых есть ключи email, alt_email, reg_mail, также есть ещё другие ключи (например state_id или "field": "sex"), не относящиеся к email.

+---+---------------+
| id | comment
+---+---------------+
| 1 | {"value": "updated", "comment": "email: cyrilpoizot@hotmail.fr => cyrilpoizot@hotmail.com; alt_email: Cyrilpoizot@hotmail.fe => ; reg_email: => reg@mail.su"; state_id: AB => BC}
| 2 | {"raw": {"fields": [{"field": "alt_email", "new_value": "", "old_value": "john.hudson225038@aol.com"}, {"field": "email", "new_value": "mail@mail.sdf", "old_value": "son129684@aol.com"}, {"field": "reg_email", "new_value": "", "old_value": "son12@gmail.com"}, {"field": "sex", "new_value": "female", "old_value": "male"}]}, "value": "updated", "comment": ""}

Необходимо из колонки comment извлечь уникальные e-mails. Т. е. в результате отработки запроса, у меня должен получиться вывод:

cyrilpoizot@hotmail.fr
cyrilpoizot@hotmail.com
Cyrilpoizot@hotmail.fe
reg@mail.su
john.hudson225038@aol.com
mail@mail.sdf
son129684@aol.com
son12@gmail.com
...
Рейтинг: 0 / 0
04.10.2021, 09:35
| Ответить | Цитировать | Написать  
Из JSON извлечь значения
    #40101777
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Пример данных выложите в виде CREATE TABLE + INSERT INTO скриптов. И не стесняйтесь использовать теги оформления (SRC) и кнопку предварительного просмотра.
...
Рейтинг: 0 / 0
04.10.2021, 13:19
| Ответить | Цитировать | Написать  
Из JSON извлечь значения
    #40102226
cupko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
cupko
Гость
угу, спасибо, в добавок нашел опечатку в json..., поправил

добавил запрос к БД для "создания" и "наполнения" таблицы

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
create table parse_json
(
	id int(10) unsigned,
   `comment` json
);

insert into parse_json set id=1, `comment`='{"value": "updated", "comment": "email: cyrilpoizot@hotmail.fr => cyrilpoizot@hotmail.com; alt_email: Cyrilpoizot@hotmail.fe => ; reg_email: => reg@mail.su; state_id: AB => BC"}';
insert into parse_json set id=2, `comment`='{"raw": {"fields": [{"field": "alt_email", "new_value": "", "old_value": "john.hudson225038@aol.com"}, {"field": "email", "new_value": "mail@mail.sdf", "old_value": "son129684@aol.com"}, {"field": "reg_email", "new_value": "", "old_value": "son12@gmail.com"}, {"field": "sex", "new_value": "female", "old_value": "male"}]}, "value": "updated", "comment": ""}';
...
Рейтинг: 0 / 0
06.10.2021, 08:38
| Ответить | Цитировать | Написать  
Из JSON извлечь значения
    #40102233
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
WITH RECURSIVE 
cte AS ( SELECT id,
                TRIM(CAST(TRIM( BOTH '"' FROM REGEXP_SUBSTR(comment, '[ :"][A-Za-z0-9.]+@[A-Za-z0-9.]+[ ;"]')) AS CHAR)) email,
                SUBSTRING(comment FROM 1 + LOCATE('@', comment)) comment
         FROM parse_json 
       UNION ALL
         SELECT id,
                TRIM(TRIM( BOTH '"' FROM REGEXP_SUBSTR(comment, '[ :"][A-Za-z0-9.]+@[A-Za-z0-9.]+[ ;"]'))),
                SUBSTRING(comment FROM 1 + LOCATE('@', comment))
         FROM cte
         WHERE LOCATE('@', comment) )
SELECT DISTINCT email
FROM cte;


https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=522fa4b7415edb73f9f7e61e553cb59f
...
Рейтинг: 0 / 0
06.10.2021, 09:16
| Ответить | Цитировать | Написать  
Из JSON извлечь значения
    #40102292
cupko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
cupko
Гость
спасибо за ответ, но к сожалению, решение не подходит. версия mysql, где хранятся данные - 5.7.33.

если верно понял, решение выше - работает начиная с версии mysql 8 ...
...
Рейтинг: 0 / 0
06.10.2021, 11:09
| Ответить | Цитировать | Написать  
Из JSON извлечь значения
    #40102297
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
В пятой версии - создавай опорную синтетическую таблицу чисел и используй её в комбинации с SUBSTRING_INDEX по "собаке". Но получится тот ещё монстр...уж больно дебильный формат у исходных данных.
...
Рейтинг: 0 / 0
06.10.2021, 11:38
| Ответить | Цитировать | Написать  
6 сообщений из 6, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Из JSON извлечь значения
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&gotonew=1&tid=1827924]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 167ms
get topic data:
 13ms
get first new msg:
 8ms
get forum data:
 3ms
get page messages:
 53ms
get tp. blocked users:
 2ms
others: 12ms
total:  288ms
созд. / загр.: 288ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]