I have a MariaDB table including a JSON field:
{
arrayOfDates : ['2020-01-11', '2021-01, 12', '2019-03-12'],
...
}
For each record in my-table
I need to extract the field arrayOfDates and pick the min value in the array.
Note I’m using MariaDB v 10.5.x so I cannot use JSON_TABLE.
How can I achieve this?
2
Answers
You can do it using a recursive CTE:
Demo here
see :
In MariaDB 10.5, if you cannot use JSON_TABLE,
However, you can use the combination of JSON functions and regular SQL. Here’s an example query to get the minimum value from the arrayOfDates field for each record:
Replace
your_table
with the actual name of your table andjson_column
with the name of your JSON column.This query uses a subquery to extract the
arrayOfDates
from the JSON column usingJSON_TABLE
. It then uses theMIN
function to find the minimum value after casting the dates to theDATE
type.