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