JSON_EXTRACT for a range of dates (MYSQL)

In this article, we will explore the use of JSON_EXTRACT in MySQL to extract data from a JSON object. We will focus on how to query a range of dates using this function.

Introduction to JSON_EXTRACT

The JSON_EXTRACT function is used to extract values from a JSON object. It takes two arguments: the JSON object and the path to the value you want to extract.

SELECT JSON_EXTRACT(json_object, 'path/to/value');

In our case, we have a JSON object with dates as keys and arrays of values as values.

The Problem

Our goal is to extract all values for a specific date range. We can’t use the JSON_EXTRACT function directly because it only allows us to specify one path to the value we want to extract.

We’ll explore different approaches to solve this problem.

Using JSON_TABLE

One approach is to use the JSON_TABLE function, which allows us to transform a JSON object into a table format. This can be done by using the json_keys() function, which returns an array of keys from the JSON object.

SET @json = '{
  "2014/12/10": [
    22.12323,
    1212312.36
  ],
  "2014/12/11": [
    24.983516,
    59239590.36
  ],
  "2014/12/15": [
    24.353891,
    10350984.54
  ],
  "2014/12/16": [
    24.756853,
    51752318.09
  ],
  "2014/12/17": [
    24.782038,
    31848161.91
  ]
}';

SELECT a.dateStr, json_extract(@json, concat('$."', a.dateStr, '"')) as vals
FROM json_table(json_keys(@json), '$[*]' COLUMNS (dateStr CHAR(10) PATH '$')) a
WHERE a.dateStr BETWEEN '2014/12/11' AND '2014/12/16';

In this example, we use json_keys() to get an array of keys from the JSON object. We then use json_table() to transform the array into a table format.

Using BETWEEN Operator

Another approach is to use the BETWEEN operator in combination with JSON_EXTRACT. However, as mentioned earlier, JSON_EXTRACT only allows us to specify one path to the value we want to extract.

Let’s take a closer look at how this works.

SELECT JSON_EXTRACT(json_object, 'dateStr:val') AS resultado
FROM tabla_prueba
WHERE json_object ->> 'dateStr' BETWEEN '2014/12/11' AND '2014/12/16';

In this example, we use the ->> operator to get a single value from the JSON object. We then use the BETWEEN operator to filter the results.

However, as you can see, this approach has its limitations. It only allows us to extract one value for each date range.

Using JOIN and GROUP BY

Another approach is to use a combination of JOIN, GROUP BY, and aggregation functions to solve this problem.

SET @json = '{
  "2014/12/10": [
    22.12323,
    1212312.36
  ],
  "2014/12/11": [
    24.983516,
    59239590.36
  ],
  "2014/12/15": [
    24.353891,
    10350984.54
  ],
  "2014/12/16": [
    24.756853,
    51752318.09
  ],
  "2014/12/17": [
    24.782038,
    31848161.91
  ]
}';

SELECT t1.dateStr, GROUP_CONCAT(t2.val) AS vals
FROM json_table(json_keys(@json), '$[*]' COLUMNS (dateStr CHAR(10) PATH '$')) t1
JOIN (
  SELECT dateStr, val
  FROM @json
  WHERE dateStr BETWEEN '2014/12/11' AND '2014/12/16'
) t2 ON t1.dateStr = t2.dateStr
GROUP BY t1.dateStr;

In this example, we use a combination of JOIN and GROUP BY to solve the problem. We first get all values for each date range using a subquery. Then, we join the table with itself on the date column.

Conclusion

In conclusion, while JSON_EXTRACT does not directly support querying a range of dates, we can use different approaches such as JSON_TABLE, BETWEEN operator, and combining JOIN, GROUP BY, and aggregation functions to solve this problem. Each approach has its own limitations, and the best solution depends on your specific use case.

Recommendations

If you need to frequently query a range of dates from a JSON object, it’s recommended that you consider storing the data in a relational database instead. This will allow you to leverage the power of SQL querying and indexing to improve performance.

Alternatively, if you’re stuck with MySQL for now, we recommend exploring other approaches such as using JSON_TABLE or combining JOIN, GROUP BY, and aggregation functions to solve the problem.

Last modified on 2023-10-10