Retrieving min value in json array from MariaDB table

Question

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?

Answer 1

You can do it using a recursive CTE:

with recursive cte as (
      select '            ' as dt, REPLACE(REPLACE(JSON_QUERY(myjson, '$.arrayOfDates'), '[', ''), ']', ',') as dates, 1 as lev
      from mytable
      union all
      select substring_index(dates, ',', 1),
             substr(dates, instr(dates, ',') + 2), lev + 1
      from cte
      where dates like '%,%'
)
select MAX(dt) AS max_date
from cte
where lev > 1;

Demo here

see :

• SQL (Maria DB) split string separated by comma to rows

Answer 2

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:

    SELECT 
        your_table.*, 
        (SELECT MIN(CAST(date AS DATE)) 
         FROM JSON_TABLE(your_table.json_column, '$.arrayOfDates[*]' 
         COLUMNS (date CHAR(10) PATH '$'))) AS min_date
    FROM 
        your_table;

Replace your_table with the actual name of your table and json_column with the name of your JSON column.

This query uses a subquery to extract the arrayOfDates from the JSON column using JSON_TABLE. It then uses the MIN function to find the minimum value after casting the dates to the DATE type.

Answer 3

Assuming your data is like this:

create table tbl (jsn text);
insert into tbl (jsn) values 
  ('{"arrayOfDates":["2020-01-11", "2021-01-12", "2019-03-12"],"other":[0,1]}');

You can use a recursive CTE to "loop" through the JSON array and extract every single date.

with recursive rcte (seq, jsn, dt) as (
  select 0, jsn, json_extract(jsn, '$.arrayOfDates[0]')
  from tbl
  union all
  select seq + 1, jsn, json_extract(jsn, replace('$.arrayOfDates[?]', '?', seq+1))
  from rcte
  where json_extract(jsn, replace('$.arrayOfDates[?]', '?', seq+1)) is not null
)
select * from rcte

seq	jsn	dt
0	{"arrayOfDates":["2020-01-11", "2021-01-12", "2019-03-12"],"other":[0,1]}	"2020-01-11"
1	{"arrayOfDates":["2020-01-11", "2021-01-12", "2019-03-12"],"other":[0,1]}	"2021-01-12"
2	{"arrayOfDates":["2020-01-11", "2021-01-12", "2019-03-12"],"other":[0,1]}	"2019-03-12"

fiddle

This query is based on a recursive sequence query. It starts from the first array element (at index/position 0) and loops "recursively" through the sequence numbers and the according array elements. Our recusion stop condition is the absence of an array element at the position of the sequence number (where json_extract([..] seq+1)) is not null). A single date is extracted with the JSON_EXTRACT function. The sequence number ('seq+1') is "injected" into the JSON path using the REPLACE function.

In the outer query you can now select MIN, MAX or what ever you want from the above result.

create table tbl (jsn text);
insert into tbl (jsn) values 
  ('{"arrayOfDates":["2020-01-11", "2021-01-12", "2019-03-12"],"other":[0,1]}');

with recursive rcte (seq, jsn, dt) as (
  [..]
)
select jsn, min(dt) as min_dt
from rcte
group by jsn

jsn	min_dt
{"arrayOfDates":["2020-01-11", "2021-01-12", "2019-03-12"],"other":[0,1]}	"2019-03-12"

fiddle