Как выбрать значение по наибольшему ключу из объекта json?

Question

[KirSupi]

Есть колонка с JSON, там в объекте по датам разбиты числа. Надо выбрать число с наибольшей датой (наибольшим ключом).
Пример:

{"2022-01-01": 4, "2022-05-05": 10, "2022-09-09": 9}

Отсюда надо вытащить 9, т.к. 2022-09-09 - наибольший ключ
Может быть есть какие-то функции подобные col->>"$.[MAX(*)]"?

Answer 1

[Ипатьев]

Традиционный ответ: Не хранить в JSON данные, которые для этого не предназначены.
А хранить их нормально - в предназначенной для этого связанной таблице.

Answer 2

[Rsa97]

# Schema (MySQL v8.0)
CREATE TABLE `test` (
  `id` INT,
  `json` JSON
);
INSERT INTO `test` VALUES
  (1, '{"2022-01-01": 4, "2022-05-05": 10, "2022-09-09": 9}'),
  (2, '{"2022-05-01": 4, "2022-01-05": 10, "2022-03-09": 9}');

# Query #1
WITH `cte` AS (
  SELECT `t`.`id`, MAX(`j`.`json_key`) AS `key`
    FROM `test` AS`t`
    JOIN JSON_TABLE(
      JSON_KEYS(`json`), '$[*]' COLUMNS(`json_key` JSON PATH '$')
    ) AS `j`
    GROUP BY `t`.`id`
)
SELECT `t`.`id`, `c`.`key`, 
       JSON_EXTRACT(`t`.`json`, CONCAT('$.', `c`.`key`)) AS `val`
  FROM `cte` AS `c`
  JOIN `test` AS `t` ON `t`.`id` = `c`.`id`

# Result
| id  | key          | val |
| --- | ------------ | --- |
| 1   | "2022-09-09" | 9   |
| 2   | "2022-05-01" | 4   |

Comments

[Akina]

Можно и без CTE:

SELECT test.id, 
       MAX(jsontable.`key`) `key`, 
       JSON_EXTRACT(test.json, CONCAT('$."', MAX(jsontable.`key`), '"')) value
FROM test
CROSS JOIN JSON_TABLE(JSON_KEYS(test.json),
                      '$[*]' COLUMNS (`key` DATE PATH '$')) jsontable
GROUP BY test.id, test.json