How to find birthdays in json set?

Question

MySQL

The database stores children's birthdays in json format, for example

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

How to find users who have children happy birthday +- 7 days from current date?

Answer 1

It is better to write children in a separate table.
And if you really want to, then just collective farm like this:

-- 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)