0

I'm running MariaDB 10.2.29, and started exploring JSON data types. I'm trying to understand how to group by dynamic JSON keys, and sum up their nested values. For example, the table contains the following in the 'data' field entries:

MariaDB [vstats]> select data from top_domains where vtime >= '1573666140' and vtime <= '1573666200' limit 2\G
*************************** 1. row ***************************
 data: {"domain1.com":{"total":"260","cached":"153"},"domain2.com":{"total":"1501","cached":"1170"},"domain3.com":{"total":"212","cached":"190"},"domain4.com":{"total":"1206","cached":"1118"},"domain5.com":{"total":"353","cached":"285"},"domain6.com":{"total":"216","cached":"41"},"domain7.com":{"total":"112","cached":"78"},"domain8.com":{"total":"220","cached":"194"},"domain9.com":{"total":"95","cached":"62"},"domain10.com":{"total":"99","cached":"18"}}
*************************** 2. row ***************************
 data: {"domain1.com":{"total":"240","cached":"166"},"domain2.com":{"total":"1644","cached":"1279"},"domain3.com":{"total":"195","cached":"174"},"domain4.com":{"total":"1276","cached":"1185"},"domain12.com":{"total":"84","cached":"72"},"domain5.com":{"total":"373","cached":"258"},"domain6.com":{"total":"92","cached":"13"},"domain7.com":{"total":"148","cached":"102"},"domain11.com":{"total":"217","cached":"25"},"domain8.com":{"total":"380","cached":"345"}}
2 rows in set (0.00 sec)

MariaDB [vstats]>

The 'vtime' field contains epoch values from when the stats were recorded. What I'm trying to get in return is a JSON object, grouped by domain names/JSON keys, with sums for the "total" and "cached" values. I started down a path using a nested select against the json_keys(data,'$') values, but it didn't seem optimal for transversing possibly thousands of entries.

The main reasons I have for wanting to use the JSON table type are:

  • Input data comes from a Perl hash encoded to a JSON object
  • Output data is parsed and plotted using jQuery
  • Reduced table size (I tried using normal columns and primary keys on vtime and domains but it grows far too fast)
  • Use a dataset format which doesn't have to be manipulated for input/output

Any pointers would be greatly appreciated.

Andrei
  • 23
  • 1
  • 8
  • I suspect a 4-column table (vtime, domain, total, cached) would take less disk space. And be much easier to manipulate. Normalizing `domain` would help further. What do you mean by "grows too fast? Faster than 100 rows/sec? – Rick James Nov 14 '19 at 01:50
  • The disk size isn't as big of a concern as much as the row count. Each minute there's 10 rows added, so steering away from (vtime,data) using JSON, I end up with 10x the number of rows, and then the data has to be encoded into JSON anyways for output – Andrei Nov 15 '19 at 13:31
  • If I were to take the 4-column table route, is there a more optimal way of doing this? `select domain,sum(cached) as cached,sum(total) as totals from tmp_myisam_domains where vtime > unix_timestamp(now() - interval 1 hour) group by domain order by totals desc limit 10;` and this? `select domain,Z.cached,Z.totals,concat(round(( Z.cached/Z.totals * 100 ),2),'%') AS percentage from (select domain,sum(cached) as cached,sum(total) as totals from tmp_myisam_domains where vtime > UNIX_TIMESTAMP(now() - INTERVAL 1 hour) group by domain)Z order by Z.totals desc limit 10;` – Andrei Nov 15 '19 at 15:16
  • Inserting 100 rows per minute is no problem. Even on a cheap system 100 per _second_ is possible. – Rick James Nov 15 '19 at 18:10
  • MariaDB might have a suitable JSON function that would avoid needing `CONCAT` to build the string. – Rick James Nov 15 '19 at 18:12
  • It's not about being able to insert that many rows per minute. It's about keeping an optimal schema, and using JSON, which is already supported by the database. I don't want to have 4 columns and 10 times the number of rows if I can avoid it. At this rate I might as well just use MongoDB – Andrei Nov 16 '19 at 20:30

0 Answers0