Как найти дни рождения в json наборе?

Question

[Paka]

В базе данных хранятся дни рождения детей в json формате, например

`user`.`children` = '["2018-08-15","2018-10-23","2012-06-09"]'

Как найти пользователей у которых есть дети с днем рождения +- 7 дней от текущей даты?

Comments

[Василий Банников]

https://dev.mysql.com/doc/refman/8.0/en/json.html
Но искать данные в json-полях дорого достаточно. Лучше переделать это на таблицы с джоинами

Answer 1

[alexalexes]

Лучше детей писать в отдельную таблицу.
А если очень хочется, то только так колхозить:

-- MySQL
select u.user_id, child_bdays.bday
  from user u,
        JSON_TABLE(u.children, '$[*]' COLUMNS (
                bday VARCHAR(10)  PATH '$'
         )) child_bdays
       where STR_TO_DATE(child_bdays.bday, "%Y-%m-%d") >= DATE(now() - INTERVAL 7 DAY)
         and STR_TO_DATE(child_bdays.bday, "%Y-%m-%d") <= DATE(now() + INTERVAL 7 DAY);

-- MariaDB
select u.user_id, JSON_EXTRACT(u.children, concat('$[', idx_table.idx, ']')) as bday
  from user u,
     (select 0 idx union select 1 union select 2 union
      select 3 union select 4 union select 5 union
      select 6 union select 7 union select 8 union
      select 9 union select 10 union select 11) idx_table /* впомогательная выборка для генерации индексов в диапазоне 0...11 */
  where idx_table.idx < json_length(u.children) /* отсекаем обращения к несуществующим индексам элементов в JSON*/
    and STR_TO_DATE(JSON_EXTRACT(u.children, concat('$[', idx_table.idx, ']')), "%Y-%m-%d") >= DATE(now() - INTERVAL 7 DAY)
    and STR_TO_DATE(JSON_EXTRACT(u.children, concat('$[', idx_table.idx, ']')), "%Y-%m-%d") <= DATE(now() + INTERVAL 7 DAY)

Comments

[Paka]

Жаль что это не работает для mariadb

[alexalexes]

Да, в MariaDB не реализован JSON_TABLE. Но можно воспользоваться любой существующей таблицей, чтобы сгенерировать порядковые индексы для строк в idx_table. Потом извлечь нужный элемент из JSON по индексу с помощью функции JSON_EXTRACT. Для вашей задачи достаточно 10-20 строк вспомогательной таблицы, можно даже использовать статично накиданные select ... union, как я дополнил выше ответ.

[Paka]

alexalexes,
знатный монстр конечно вышел, но спасибо вам.

в итоге я сделал пока так

SELECT
         *
    FROM
        `user`
    WHERE
        `children` REGEXP '(-11-26")|(-11-27")|(-11-28")|(-11-29")|(-11-30")|(-12-01")|(-12-02")|(-12-03")|(-12-04")|(-12-05")|(-12-06")|(-12-07")|(-12-08")|(-12-09")|(-12-10")'

где выражение для REGEXP делаю на пхп, те. вне SQL