0

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

  • Temperature (1,305)
  • Light (845)
  • Battery (243)

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
fistameeny
  • 1,048
  • 2
  • 14
  • 27

1 Answers1

1

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)
Manticore Search
  • 1,462
  • 9
  • 9