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...
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] |
+---------------------------------------------+
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.