0

I am finding that documentation for working with MySQL JSON data is still at this point apparently at it's infancy. Either that or I'm doing it wrong. I found several docs online, i.e. https://benjaminlistwon.com/blog/working-with-json-data-in-mysql-part-1-of-3/ and others - but neither are working for me.

I recently switched from storing the station data in separate tables per station (4,100+ tables I had to do a "dynamic" PHP query on to get the data - wow!) - to JSON for speed and performance reasons, and an added benifit of not doing 4200 table writes per hour.

To get the aggregate data it's much faster - for a single row of data I can just iterate through the JSON keys. Problem is that to pull data from 83+ rows where Key=Value, PHP says out of memory error. My guess is that means I need to match the JSON value in the query instead of in PHP.

Below: SQL dump of a JSON based data table, StationData. Weather data from various stations collected hourly.

https://drive.google.com/open?id=0B6ScTEIoK3tPbGhrZkJOZmhRUTQ

Problem I'm having is features like JSON_EXTRACT, JSON_SEARCH, and JSON_etc are just returning NULL.

For example, for Olathe Kansas, I want every data stored under {"KOJC":{"Temperature":"25", "Dewpoint":"23"}} etc etc - but nothing from any other keys. The key is KOJC. (for giggles tried the MySQL doc example):

select JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+
1 row in set (0.02 sec)

select JSON_KEYS(jsonData) from StationData;
+---------------------+
| JSON_KEYS(jsonData) |
+---------------------+
| NULL                |

etc... 85 rows blah blah

select JSON_EXTRACT(jsonData, "$.KOJC") from StationData;

+----------------------------------+
| JSON_EXTRACT(jsonData, "$.KOJC") |
+----------------------------------+
| NULL                             |
| NULL                             |

etc.... 85 rows in set (0.16 sec)

select jsonData->>'$.KOJC' as Station from StationData;
+---------+
| Station |
+---------+
| NULL    |

etc.... 85 rows in set (0.14 sec)

select * from StationData were JSON_SEARCH(jsonData, 'all', '$.KOJC');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON_SEARCH(jsonData, 'all', '$.KOJC')' at line 1

select * from StationData where JSON_EXTRACT(jsonData, "$.KOJC");
Empty set (0.15 sec)

UPDATE - I found what's happening. JSON_EXTRACT works for a non-arrayed JSON string but when working with an array I am unable to pull out array elements.

select JSON_EXTRACT('{"KOJC":{"Tmp":"25"}}', '$.KOJC');                          +-------------------------------------------------+
| JSON_EXTRACT('{"KOJC":{"Tmp":"25"}}', '$.KOJC') |
+-------------------------------------------------+
| {"Tmp": "25"}                                   |
+-------------------------------------------------+
1 row in set (0.01 sec)

^ that works But the below does not when presented with a array:

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$.KOJC');
 +--------------------------------------------------------------------------+
| JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$.KOJC') |
+--------------------------------------------------------------------------+
| NULL                                                                     |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$[KOJC][*]');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$[$.KOJC][*]');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.
1

Do not have 4100 identical tables. There are so many issues with such, and it is repeatedly panned in this and other forums.

4100 "stations", each with, say 10 readings, per hour? 41000 is about 12/second (average), which is not very much if you had one row per datapoint per station. One table with 3 columns: hour (or datetime), station_id, reading. And batch the 10 rows in a single INSERT statement for a little more efficiency.

How you can fetch the data in a variety of ways -- "just temperatures at 6am on weekends" becomes a simple, and somewhat efficient query.

Alternatively, have one table with 12 columns: hour/datetime, station_id, and 10 specific readings. This is likely to be more efficient in all ways. This also has the advantage that the readings can have specific datatypes for each measurement. Temperature or humidity: TINYINT SIGNED is 1 byte and allows -128..127 or DECIMAL(5.2) (3 bytes) for +/-999.99 instead of a generic FLOAT (4 bytes).

The alternatives probably take a similar amount of disk space. This database will be rather big after a year; you should probably estimate the future size and think ahead about indexing, and other issues.

  • That did nothing to address my question regarding MySQL JSON querying. – Anthony S. Jan 15 '17 at 17:56
  • True. I should have started with, "Maybe JSON is the wrong solution for your task." What version are you using? New features have been added recently. (No, I don't know if the latest version would address your JSON question.) – Rick James Jan 15 '17 at 18:01
  • mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper – Anthony S. Jan 15 '17 at 18:12
  • So the problem is that JSON_EXTRACT apparently is not returning nested elements based off a passed key. See my update. Maybe again I'm doing it wrong. – Anthony S. Jan 15 '17 at 18:46
  • I did find if I use '$**.Key' json_extract works for verry small arrays but is very slow. It crashed Apache2 when I tried to get a key out in a PDO bound query. – Anthony S. Jan 15 '17 at 23:40
0

If you need to extract JSON based on a key within each object of a Array, then you have to use $[*].key wildcard instead of $**.key as the previous is meant for Array while the later is for deep traversal (which searches each and every key no matter how deep it is).

JSON_EXTRACT

SELECT JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$[*].KOJC');

Using [*] wildcard in the datadump you've provided

SELECT SQL_NO_CACHE JSON_EXTRACT(jsonData, '$[*].KOJC') FROM `StationData`;
-- Remove the SQL_NO_CACHE in production as it meant only for performance testing

85 rows in set (0.27 sec)
-1

So I found the root cause of my problems.

I was adding unneeded complexity to my JSON array from generation.

I was storing in a [{"key":{"subkey":"val","subkey":"val"}}, {"key":{"subkey":"val","subkey":"val"}}]

I was having to find the data in MySQL using json_extract(field, '$**.key') - which was taking roughly 30 seconds to scan the data on 1 single row.

I took the [ ] master array out, now I have the data in a {"key":{"subkey":"val","subkey":"val"}}, {"key":{"subkey":"val","subkey":"val"}} array structure - I can access the data using '$.key' instead of '$**.key' - and it's instant.

Guessing this is a bug or something really screwed up in the way MySQL searches for a key - but this fixed it.

  • {"key":{"subkey":"val","subkey":"val"}}, {"key":{"subkey":"val","subkey":"val"}} is not a valid array. You should first refer the official documentation dev.mysql.com/doc/refman/5.7/en/json.html – The Coder Jan 17 '17 at 5:28
  • The problem here is that the array format shown in the documentation does not work - the original [ { "key" : { "key" : "value" } } ] format I was using. Querying based on '$.Key' returns null, and querying based on '$**.Key' takes 30 seconds with a 1 row constraint. By putting it into the non-proper format, I can then properly query on '$.Key', instantly. – Anthony S. Jan 17 '17 at 14:38
  • I wasn't crazy! I don't know if you guys actually reviewed/tested,but apparently Oracle has reproduced this. bugs.mysql.com/bug.php?id=84523 – Anthony S. Jan 18 '17 at 22:23
  • Yes, but the conclusion seems that it is not a bug, but the wrong usage of the wildcard search. – til_b Jan 19 '17 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.