Добрый день
есть записи в таблице
нужно из строки 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