SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 17.12.2021 19:29:23

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

JSON: как посчитать min/max, сумму и другие агрегирующие значения

Внезапно я осознал, что не понимаю, как из поля типа JSON выбрать, например, наибольшее из значений.

Код:

CREATE TABLE test (
    i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    j JSON
);

INSERT INTO test (j) VALUES
    ('[10, 20, 30]'),
    ('[100, 500]');

Код:

mysql> SELECT * FROM test;
+---+--------------+
| i | j            |
+---+--------------+
| 1 | [10, 20, 30] |
| 2 | [100, 500]   |
+---+--------------+

Как выглядит запрос, который для каждой записи выберет максимальное число из массивов в колонке j?

Неактивен

 

#2 17.12.2021 20:30:33

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2386

Re: JSON: как посчитать min/max, сумму и другие агрегирующие значения

LazY, полагаю, тебе сюда смотреть
https://dev.mysql.com/doc/refman/8.0/en … json-table


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#3 18.12.2021 11:17:33

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3873

Re: JSON: как посчитать min/max, сумму и другие агрегирующие значения

Похоже, что такого типа функции в MySQL еще не поддерживаются. Задача похожа на вычисление суммы элементов массива JSON. Для этого предлагают написать хранимую функцию:
https://stackoverflow.com/a/45297362
Функция для MAX полностью аналогична.

Неактивен

 

#4 31.01.2022 19:21:12

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: JSON: как посчитать min/max, сумму и другие агрегирующие значения

Единственное решение здесь действительно через JSON_TABLE(), как все и говорили.

На мой взгляд, серьезная недоработка со стороны разработчиков MySQL:

1. Нужно добавлять в запрос не только дополнительную таблицу, но еще и группировку. Причем сходу не разберешься, как такой запрос надо писать, потому что JOIN тут необычный, а специально мутировавший для конкретно этого случая.

2. Таким образом нельзя построить виртуальные колонки и, как следствие, индексы по ним. Если нужно по такому сортировать, придется гонять по всей таблице hmm

Оставлю тут наглядный пример. Может, кому пригодится:


# 1. Создаём таблицу:
CREATE TABLE json_test (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

# 2. Вставляем одну запись с JSON-документом из трёх строк-объектов:
INSERT INTO json_test (data)
VALUES ('[{"value":1000}, {"value":2000}, {"value":3000}]');

# 3. Выбираем данные:
SELECT
    id,
    SUM(j.cost) # j - псевдоним таблицы-результата JSON_TABLE()
FROM json_test t
JOIN JSON_TABLE(
    t.data, # t - псевдоним главной таблицы
    '$[*]' COLUMNS ( # Здесь указываем путь к корню JSON-документа
        cost INT PATH '$.value' # Здесь снова указываем путь, но уже относительно "строки" данных, а не корня документа
    )
) AS j # Нет части ON; её функцию выполняет упоминание главной таблицы в первом аргументе JSON_TABLE
GROUP BY id # Результат группируем!
 

Неактивен

 

#5 08.08.2022 00:33:43

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: JSON: как посчитать min/max, сумму и другие агрегирующие значения

Другой заковыристый пример - представление ассоциативного массива в виде таблицы.

Тут сложность в том, что ключи массива нельзя получить там же, где значения. Из-за этого нужно строить довольно замысловатые конструкции.


# Вставляем две строки, содержащие JSON ассоциативного массива из трёх элементов:
INSERT INTO json_test (data)
VALUES
    ( '{ "a": 1000, "b": 2000, "c": 3000 }' ),
    ( '{ "d": 4000, "e": 5000 }' ) ;

# Хотим построить запрос, где в одном столбце будут все ключи,
# а в другом - соответствующие им значения.
# Вот как он выглядит:

SELECT
    json_test.id,
    j1.n,
    j1.key,
    j2.value
FROM json_test
JOIN JSON_TABLE(
    JSON_KEYS(data),
    '$[*]' COLUMNS (
        n FOR ORDINALITY,
        `key` VARCHAR(32) PATH '$'
    )
) AS j1
JOIN JSON_TABLE(
    data,
    '$.*' COLUMNS (
        n FOR ORDINALITY,
        value JSON PATH '$'
    )
) AS j2 ON j1.n = j2.n
 


Код:

+----+------+------+-------+
| id | n    | key  | value |
+----+------+------+-------+
|  1 |    1 | a    | 1000  |
|  1 |    2 | b    | 2000  |
|  1 |    3 | c    | 3000  |
|  2 |    1 | d    | 4000  |
|  2 |    2 | e    | 5000  |
+----+------+------+-------+

Здесь нужно иметь в виду следующие особенности:

1. Для получения ключей потребовался дополнительный JOIN с JSON_TABLE, причем в качестве корневого JSON-документа ему передается не сам массив, а его ключи - JSON_KEYS().

2. В обоих JOIN присутствует столбец типа FOR CARDINALITY.

Это специальный автоинкрементный столбец, значения которого генерируются автоматически. Для наглядности он включен в результат запроса.

В данном случае этот столбец понадобился, чтобы связать между собой два JOIN.
документации такое его использование не упоминается. Спасибо автору найденного на stackoverflow комментария.)

3. В качестве пути к значениям массива указан $., а не $[*], поскольку массив ассоциативный, а не числовой.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson