MySQL join in a JSON array

Question

I have these rows in mysql table

id  categoryNameSp    categoryNameEn
1   Comida            Food
2   Fruta             Fruit
3   Fruta Seca        Dried fruit

And then i have this row in another table

pid  path
1    ["1", "2", "3"]

I want to return the path but instead of numbers i want to return categoryNameEn so the return will be:

  pid  path
  1    ["Food","Fruit", "Dried fruit"]

Out of curiosity, why did you decide to store the paths in a JSON array, if you wanted to do queries like this that treat the array elements as discrete values? — Bill Karwin
– Bill Karwin, Commented Jan 23, 2021 at 17:22
Sorry for the late reply, the goal was to allow a category search that would consider all selected categories just like a windows folder system would. So client side i store all the selected categories and then return the fruit with mysql function json_contains(). — Jo Hassan
– Jo Hassan, Commented Jan 26, 2021 at 13:45
But why not store each path value on its own row? Then it would be a lot easier to do the join to the category table, without using complex JSON functions. — Bill Karwin
– Bill Karwin, Commented Jan 26, 2021 at 17:19
By doing so I would have been forced to determine the number of columns and therefore I would have had to determine a maximum number of subcategories but with the json I manage everything on the client side and I can manage it if I detect that the path is over — Jo Hassan
– Jo Hassan, Commented Jan 27, 2021 at 12:52

geertjanvdk · Accepted Answer · 2021-01-23 17:14:40Z

This would require to use the JSON_TABLE functionality as well as the JSON_ARRAYAGG aggregation function.

Recreating your situation using the following DML and DDL:

CREATE TABLE categories (id INT, name VARCHAR(30));
INSERT INTO categories VALUES (1, 'Food'), (2, 'Fruit'), (3, 'Dried Fruit');

CREATE TABLE stuff (pid INT, path JSON);
INSERT INTO stuff VALUES (1, '["1", "2", "3"]');

Then we can do the following (fancy) query:

SELECT pid, JSON_ARRAYAGG(c.name) FROM stuff AS s
    JOIN JSON_TABLE(s.path, '$[*]' COLUMNS (category INT PATH '$')) AS cats
    JOIN categories AS c ON cats.category = c.id
    GROUP BY pid;

The idea is to create a table from the JSON data found in the stuff table. We then join it with the categories tables, and aggregate the result into a JSON array.

The result:

+------+----------------------------------+
| pid  | JSON_ARRAYAGG(c.name)            |
+------+----------------------------------+
|    1 | ["Food", "Fruit", "Dried Fruit"] |
+------+----------------------------------+

Collectives™ on Stack Overflow

MySQL join in a JSON array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related