Блог

Главная - Вопросы по программированию - Значение MySQL aggr, сгруппированное по ключам json

Значение MySQL aggr, сгруппированное по ключам json

#mysql #json #group-by #mysql-5.7

#mysql #json #группировка по #mysql-5.7

Вопрос:

Как агрегировать значения JSON, сгруппированные по ключам JSON?
Версия MYSQL: 5.7.12

таблица —

  ------ -------------------------------------- 
| col1 | col2                                 |
 ------ -------------------------------------- 
| PPP  | {"A": 0, "B": 5, "C": 10}            |
| SSS  | {"A": 5}                             |
| KKK  | {"A": 5, "B": 5, "C": 10}            |
| KKK  | {"C": 20, "B": 5}                    |
 ------ --------------------------------------

вывод —

  ------ -------------------------------------- 
| col1 | col2                                 |
 ------ -------------------------------------- 
| PPP  | {"A": 0, "B": 5, "C": 10}            |
| SSS  | {"A": 5}                             |
| KKK  | {"A": 5, "B": 10, "C": 30}           |
 ------ --------------------------------------

col2 может иметь любые ключи, например, D, K, L, N и т.д.

JSON_MERGE_PATCH может помочь, но он недоступен в этой версии mysql? Может кто-нибудь помочь в этом?

Комментарии:

1. Какую версию MySQL вы используете?

2. В любом случае — разбор на отдельные строки, агрегирование, восстановление JSON. Для версии 5.7.12 — JSON_TABLE недоступен, поэтому рекомендуется хранимая процедура с обычным синтаксическим анализом строк.

3. Что такое max. количество пар ключ-значение в col2 ?

4. около 10 пар ключ-значение

Ответ №1:

Решение для не более чем 5 пар ключ-значение на JSON (может быть легко расширено):

 SELECT col1, JSON_OBJECTAGG(`key`, `value`) col2
FROM (
SELECT col1,
       JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(col2), CONCAT('$[', numbers.num, ']'))) `key`,
       SUM(JSON_EXTRACT(col2, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(col2), CONCAT('$[', numbers.num, ']')))))) `value`
FROM test
CROSS JOIN (          SELECT 0 num
            UNION ALL SELECT 1 
            UNION ALL SELECT 2 
            UNION ALL SELECT 3 
            UNION ALL SELECT 4 ) numbers
GROUP BY 1,2
HAVING `key` IS NOT NULL
) subquery
GROUP BY col1;

скрипка (содержит шаги построения решения).