Как с помощью SQL запроса выбрать из строки значения и посчитать их уникальное количество?

Question

[j238267431]

Добрый день

есть записи в таблице
нужно из строки descr выбрать все  DeviceID и посчитать сколько уникальное количество

Вот пример одной такой записи в ней 2 уникальные DeviceID otc3zdliy2q2yze2ztayzq== и mjjjmdjimta1nwrkzdrmyg==

{"Device":"Xiaomi M2101K7BG","DeviceID":"otc3zdliy2q2yze2ztayzq==","MobileOperator":"Beeline KZ 401","idout":"1"}{"Device":"Xiaomi M2101K7BG","DeviceID":"otc3zdliy2q2yze2ztayzq==","MobileOperator":"Beeline KZ 401","idout":"2"}{"Device":"Xiaomi M2101K7BG","DeviceID":"otc3zdliy2q2yze2ztayzq==","MobileOperator":"Beeline KZ 401","idout":"3"}{"Device":"Xiaomi M2101K7BG","DeviceID":"otc3zdliy2q2yze2ztayzq==","MobileOperator":"Beeline KZ 401","idout":"4"}{"Device":"Xiaomi M2101K7BG","DeviceID":"otc3zdliy2q2yze2ztayzq==","MobileOperator":"Beeline KZ 401","idout":"5"}{"Device":"Xiaomi Redmi Note 8 Pro","DeviceID":"mjjjmdjimta1nwrkzdrmyg==","MobileOperator":"Beeline KZ 401","idout":"6"}{"Device":"Xiaomi M2101K7BG","DeviceID":"otc3zdliy2q2yze2ztayzq==","MobileOperator":"Beeline KZ 401","idout":"7"}{"Device":"Xiaomi Redmi Note 8 Pro","DeviceID":"mjjjmdjimta1nwrkzdrmyg==","MobileOperator":"Beeline KZ 401","idout":"8"}{"Device":"Xiaomi M2101K7BG","DeviceID":"otc3zdliy2q2yze2ztayzq==","MobileOperator":"Beeline KZ 401","idou

Пробовал вот так, но здесь только одна строка обрабатывается и количество вхождений вручную прописано (SELECT 1 n UNION SELECT 2 итд)

SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT descr FROM z_module_logs LIMIT 160, 1), '","MobileOperator', numbers.n), ',', -1) as value
FROM (
  SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) numbers

Comments

[Сергей Горностаев]

Лучше всего не хранить JSON в поле. Если уж хочется нормализацию ломать, то хранить хотя бы в поле типа JSON. Совсем уж в крайнем случае можно результаты выборки преобразовать в JSON и воспользоваться соответствующими операторами.

[j238267431]

Сергей Горностаев,
Спасибо, а с тем что есть можно что-то сделать?

[ThunderCat]

j238267431, Можно, преобразовать таблицы в 3 нормальную форму, написать скрипт переноса данных и перестать мучить сервер. Займет пол дня, решает проблему навсегда.

[Akina]

Показанное значение - вообще чёрт знает что. Горсть JSON, объединённых в единое значение даже без разделителей. То есть всё целиком оно даже JSON не является.

Минимально можно с помощью CONCAT() и REPLACE() преобразовать это в валидный JSON и распарсить с помощью JSON_TABLE(). Но нормализация - это единственно проавильный путь.

Answer 1

[DKSoft]

Можно попробовать так

with a as  #преобоазуем в json массив
(select id,concat('[',replace(descr,'}{','},{'),']') as d1
 from x1
)
,b as ( # массив в таблицу
select id,JSON_EXTRACT(d1, '$[1]') as d2
 from a
union all
select id,JSON_EXTRACT(d1, '$[2]') as d2
 from a
union all
select id,JSON_EXTRACT(d1, '$[3]') as d2
 from a
union all
select id,JSON_EXTRACT(d1, '$[4]') as d2
 from a
union all
select id,JSON_EXTRACT(d1, '$[5]') as d2
 from a
union all
select id,JSON_EXTRACT(d1, '$[6]') as d2
 from a
union all
select id,JSON_EXTRACT(d1, '$[7]') as d2
 from a
union all
select id,JSON_EXTRACT(d1, '$[8]') as d2
 from a
union all
select id,JSON_EXTRACT(d1, '$[9]') as d2
 from a
union all
select id,JSON_EXTRACT(d1, '$[10]') as d2
 from a
)
-- подсчет
select id,count(distinct JSON_VALUE(d2,'$.DeviceID')) as count
from b
where b.d2 is not null
group by id

если в поле descr больше 10 элементиов то надо добавить в b допролнительные union all