JSON_EXTRACT for a range of dates (MYSQL)

Question

I read multiple times the MYSQL guide for JSON (https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-extract)

But I still don't find a way to query a range of dates.

I have the following stored 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]}

And the following query:

SELECT
    JSON_EXTRACT(contenido, '$."2014/12/11"', '$."2014/12/16"') AS resultado
FROM
    tabla_prueba;

I expect the result of:

[24.983516, 59239590.36],  [24.353891, 10350984.54],  [24.756853, 51752318.09]

But instead, I am getting only the two mentioned values:

[24.983516, 59239590.36], [24.756853, 51752318.09]

I also tried the key word to (for ranges) but with no results. What I am doing wrong?

JSON_EXTRACT extracts values for specified paths only. None BETWEEN can be applied. I recomment you to parse your JSON to separate values using JSON_TABLE. — Akina
– Akina, Commented Apr 9, 2022 at 7:51
This would be so much easier with a properly normalized data model. — user330315
– user330315, Commented Apr 9, 2022 at 11:32

Luuk · Accepted Answer · 2022-04-09 13:06:26Z

1

Using JSON_TABLE Try:

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';

Result:

dateStr	vals
2014/12/11	[24.983516, 59239590.36]
2014/12/15	[24.353891, 10350984.54]
2014/12/16	[24.756853, 51752318.09]

edited Apr 9, 2022 at 13:06

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

answered Apr 9, 2022 at 13:04

Soheil Rahsaz

81110 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON_EXTRACT for a range of dates (MYSQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related