Блог

У меня есть таблица со следующей структурой:

 |id | json |
------------
|   |      |
------------
 

Структура JSON выглядит следующим образом:

 {
    "roomType": "Deluxe",
    "package": "Full-Board  ",
    "comb": {
        "adult": "1",
        "infant": "0",
        "child": "0",
        "teen": "0"
    },
    "rates": [
    {
        "rateFrom": "2021-02-11",
        "rateTo": "2021-02-20",
        "ratePrice": "6000"
    }, {
        "rateFrom": "2021-02-21",
        "rateTo": "2021-02-26",
        "ratePrice": "6500"
    }]
}
 

В атрибуте может быть много записей rates .
Теперь мне нужно вернуть строки, в которых любой атрибут rateTo from rates больше сегодняшней даты.

То есть, если сегодняшняя дата меньше хотя бы одной rateTo из записей rates , то верните эту строку.

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

Это было бы намного проще, если бы вы отказались от JSON в качестве типа данных и использовали правильно нормализованные таблицы комнат, пакетов, comb (может быть, часть пакетов?) и скорости. Однако, если вы застряли с JSON, один из способов получить нужные данные — извлечь все rateTo значения из каждого JSON в разделенный запятыми (и завершающийся) список дат (например, для ваших выборочных данных это будет 2021-02-20,2021-02-26, ; затем разделите это на отдельные даты 2021-02-20 , а 2021-02-26 затем SELECT строки из исходная таблица, если одна из связанных дат после сегодняшнего дня. Вы можете сделать это с помощью нескольких рекурсивных CTE:

 WITH RECURSIVE toDate AS (
  SELECT id, CONCAT(REGEXP_REPLACE(JSON_EXTRACT(`json`, '$.rates[*].rateTo'), '[ "\[\]]', ''), ',') AS toDates
  FROM rooms
),
Dates AS (
  SELECT id, SUBSTRING_INDEX(toDates, ',', 1) AS toDate, REGEXP_REPLACE(toDates, '^[^,] ,', '') AS balance
  FROM toDate
  UNION ALL
  SELECT id, SUBSTRING_INDEX(balance, ',', 1), REGEXP_REPLACE(balance, '^[^,] ,', '')
  FROM Dates
  WHERE INSTR(balance, ',') > 0
)
SELECT *
FROM rooms r
WHERE EXISTS (SELECT *
              FROM Dates d
              WHERE d.id = r.id AND d.toDate > CURDATE())
 

Демонстрация на dbfiddle