2

I am using MYSQL 5.7.11 which supports JSON datafields.

I have a JSON field in one of my tables that stores price history of a product:

JSON structure Sample:

[{"da": "2016-05-03 08:32", "pr":15.90}] 
[{"da": "2016-03-22 09:02", "pr":14.40}]
[{"da": "2016-03-15 12:08", "pr":40.00}, {"da": "2016-06-28 10:32", "pr":42.00}]
[{"da": "2016-03-29 02:39", "pr":13.90}]
[{"da": "2016-05-03 08:38", "pr":17.90},{"da": "2016-07-19 10:18", "pr":26.80},{"da": "2016-07-19 14:20", "pr":24.80}]

As you can see it can have multiple JSON arrays inside a row. Each JSON row in the sample represents a different product. Like first ROW price 15.90 is for APPLE, second ROW 14.40 is for ORANGE and third 40.00 and 42.00 is for BANANA. Just to make this clear.

What i am looking for is to be able to search between range of dates.

Like get all products price history between Date A to Date B.

For testing purposes I tried to do something like this:

select json_extract(json_price_history, '$.pr')=13.90 from products

But it always returned many rows with just NULL.

Any help would be appreciated...

e4c5
  • 52,766
  • 11
  • 101
  • 134
Dror
  • 1,262
  • 3
  • 21
  • 34

1 Answers1

2

JsonPath.

The jsonpath in your query is looking to extract an element named pr from a JSON dictionary. But your table does not contain a dictionary. It has an array where each element is a dictionary. Thus the query should be changed as follows;

SELECT JSON_EXTRACT(DICT, '$[*].pr') FROM myjson;

This will show you something like this:

+---------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(DICT, '$[*].pr')) |
+---------------------------------------------+
| NULL                                        |
| [15.9]                                      |
| [14.4]                                      |
| [40, 42]                                    |
| [13.9]                                      |
| [17.9, 26.8, 24.8]                          |
+---------------------------------------------+

Storing arrays in an RDBMS

It's perfectly fine to store arrays in an RDBMS. However if you find yourself having to search for something in those arrays that means you have the wrong database design. I haven't seen this explicitly mentioned in the mysql docs, but postgreql where you get much [more advanced json and array support][1] is very clear about this.

Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • Thanks for the reply. Would it be possible to "extract" only rows that has at least one price higher than X and lower than Y? for example- If its bigger than 10 and lower than 18: result would be [15.9] [14.4] [13.9][17.9,26.8,24.8] – Dror Jul 23 '16 at 15:27
  • 1
    AFAIK mysql does not support testing for an element of an array to be larger (or smaller) than a constant. It only supports searching if an array contains a constant. – e4c5 Jul 23 '16 at 15:58
  • i see. shame it doesn't. I guess it will. Thanks for your answer. – Dror Jul 23 '16 at 17:16
  • Just update: I removed json totally and just using standard RDBMS for history.. Better solution for this situation. – Dror Jul 28 '16 at 06:23
  • I agree with you totally – e4c5 Jul 28 '16 at 06:28