CODEWITHSUNDEEP
jsonmariadb
Jolan
Jolan

Reputation: 711

MariaDB JSON Query

I have a table with the following structure:

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

The JSON structure is as follows:

{
    "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"
    }]
}

There can be many entries in attribute rates. Now, I need to return the rows where any of the attribute rateTo from rates is greater than today's date.

That is, if today's date is less than at least one rateTo of the entries of rates, then return that row.

This is the first time I am querying JSON and I am not sure if the structure of my JSON is correct for the type of querying I want to do.

Upvotes: 0

Views: 101

Answers (1)

Nick
Nick

Reputation: 147166

This would be much easier if you abandoned JSON as a datatype and used properly normalised tables of rooms, packages, comb (might be part of packages?) and rates. If you are stuck with JSON though, one way to get the data you want is to extract all the rateTo values from each JSON into a comma separated (and ended) list of dates (for example, for your sample data, this would be 2021-02-20,2021-02-26,; then split that into individual dates 2021-02-20 and 2021-02-26, then SELECT rows from the original table if one of the associated dates is after today. You can do this with a couple of recursive CTEs:

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

Demo on dbfiddle

Upvotes: 2

Related Questions