Dynamic Facet Counts for JSON Attributes

General Discussions - English
1

Hello everyone, i have a table with the following schema and example data:

CREATE TABLE IF NOT EXISTS products (
    attributes JSON ENGINE='columnar'
) min_infix_len = '3' ENGINE='columnar';

INSERT INTO products (id, attributes) VALUES
(1, '{"kat1000":"color:red","kat2":"form:eckig","kat3":"material:cotton"}'),
(2, '{"kat1":"color:blue","kat2":"form:rund","kat3":"material:wood"}'),
(3, '{"kat1":"color:green","kat2":"form:oval","kat3":"material:metal"}'),
...
(30, '{"kat1":"color:yellow","kat2":"form:eckig","kat3":"material:glass"}');

We would like to generate dynamic facet counts based on JSON keys such as kat1, kat2, …, katN, for example:

SELECT * FROM products FACET attributes; -- like FACET attributes[kat1, kat2, katN]

SELECT * FROM products FACET attributes.kat1;

this works but there can be many katN

questions:

  1. Is it possible to dynamically output facet counts for the JSON keys (e.g., kat1, kat2, …)? If yes, how would the query look?
  2. If this functionality is not supported, what changes to the database design would you recommend to achieve this efficiently and with good performance?
2

No, it’s not possible, you have to specify the names to group by yourself.

If this functionality is not supported, what changes to the database design would you recommend to achieve this efficiently and with good performance?

Instead of:

(3, '{"kat1":"color:green","kat2":"form:oval","kat3":"material:metal"}'),

you can do this:

(3, '{"kat": ["color:green", "form:oval","material:metal"]}')

e.g.:

mysql> drop table if exists products; CREATE TABLE IF NOT EXISTS products ( attributes JSON ENGINE='columnar' ) min_infix_len = '3' ENGINE='columnar'; INSERT INTO products (id, attributes) VALUES (1, '{"kat": ["color:red", "form:eckig", "material:cotton"]}'), (2, '{"kat": ["color:blue", "form:rund", "material:wood"]}'), (3, '{"kat": ["color:green", "form:oval", "material:metal"]}'), (30, '{"kat": ["color:yellow", "form:eckig", "material:glass"]}'); select * from products facet attributes.kat;
--------------
drop table if exists products
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE TABLE IF NOT EXISTS products ( attributes JSON ENGINE='columnar' ) min_infix_len = '3' ENGINE='columnar'
--------------

Query OK, 0 rows affected, 1 warning (0.01 sec)

--------------
INSERT INTO products (id, attributes) VALUES (1, '{"kat": ["color:red", "form:eckig", "material:cotton"]}'), (2, '{"kat": ["color:blue", "form:rund", "material:wood"]}'), (3, '{"kat": ["color:green", "form:oval", "material:metal"]}'), (30, '{"kat": ["color:yellow", "form:eckig", "material:glass"]}')
--------------

Query OK, 4 rows affected (0.00 sec)

--------------
select * from products facet attributes.kat
--------------

+------+--------------------------------------------------------+
| id   | attributes                                             |
+------+--------------------------------------------------------+
|    1 | {"kat":["color:red","form:eckig","material:cotton"]}   |
|    2 | {"kat":["color:blue","form:rund","material:wood"]}     |
|    3 | {"kat":["color:green","form:oval","material:metal"]}   |
|   30 | {"kat":["color:yellow","form:eckig","material:glass"]} |
+------+--------------------------------------------------------+
4 rows in set (0.00 sec)
--- 4 out of 4 results in 1ms ---

+-----------------+----------+
| attributes.kat  | count(*) |
+-----------------+----------+
| material:cotton |        1 |
| form:eckig      |        2 |
| color:red       |        1 |
| material:wood   |        1 |
| form:rund       |        1 |
| color:blue      |        1 |
| material:metal  |        1 |
| form:oval       |        1 |
| color:green     |        1 |
| material:glass  |        1 |
| color:yellow    |        1 |
+-----------------+----------+
11 rows in set (0.00 sec)
--- 11 out of 11 results in 1ms ---
3

Hi @Sergey Thank you for your quick response!

Would it be possible to filter the data so that, for example, only entries with kat1 are considered?

Sample data:

(3, '{"kat": ["kat1:color:green", "kat2:form:oval", "kat3:material:metal"]}')

Best regards!