I have an index of data in Manticore that includes a JSON field (called readings that is structured like this:
{
"temperature": 12.3,
"light": 45.5,
"battery": 3422,
....
}
I'm wanting to facet the results such that I can display counts of rows matching temperature, light etc. For example:
Readings
It's important that I'm able to get the "keys" of the array to facet by (as I don't want to hard code the list of keys in my query, it should ideally be dynamic).
I can't work out how to query so Manticore FACETs on the key of the array. I was hoping for something like:
SELECT * FROM readings_data FACET readings.KEYNAME;
...where KEYNAME is something magical to represent the key of the array element. Is that possible?
I can restructure my JSON array if required, to make it easier to work with.
Thanks for any help or suggestions you can offer.
It's only possible if you duplicate the keys in a JSON array:
mysql> create table fistameeny (f text, j json);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into fistameeny(j) values('{"temperature": 12.3, "light": 45.5, "battery": 3422, "keys": ["temperature", "light", "battery"]}');
Query OK, 1 row affected (0.01 sec)
mysql> insert into fistameeny(j) values('{"temperature": 13.3, "battery": 3433, "keys": ["temperature", "battery"]}');
Query OK, 1 row affected (0.00 sec)
mysql> select * from fistameeny limit 0 facet j.keys;
Empty set (0.00 sec)
+-------------+----------+
| j.keys | count(*) |
+-------------+----------+
| light | 1 |
| battery | 2 |
| temperature | 2 |
+-------------+----------+
3 rows in set (0.00 sec)