MySQL Query select concat on json field ids

Question

i have this two tables

Table 1: "Identity"

ID | identifier | report_ids
------------------------------
1  | TOP        | ["1","2"]

The "report_ids" is a VARCHAR column, not JSON

Table 2: "Reports"

ID | name
------------------------------
1  | Report Food
2  | Report Beverage

I have to select from table Identity and Join the table Reports to get the names by his ids

in summary, i hope to have this result with a query:

Result:
ID | identifier | report_names
--------------------------------
1  | TOP        | Report Food, Report Beverage

How i can do this with Query?

Thanks in advance at all :)

What is precise MySQL version? The "report_ids" is a VARCHAR column, not JSON It doesn't matter - if it is valid JSON then it can be used as an argument in JSON functions. — Akina
– Akina, Commented Jan 7, 2021 at 20:22
My version is: 10.4.10-MariaDB, i have the varchar because not all time this value is json, but i think it could be casted to json no? — MeisterC
– MeisterC, Commented Jan 7, 2021 at 20:23
If so you must show "values which are not JSON" and tell what output do you need for such rows. Or maybe you need to process only those rows which contains valid JSON values? — Akina
– Akina, Commented Jan 7, 2021 at 20:26
the second, i need to process only those rows which contains valid JSON values — MeisterC
– MeisterC, Commented Jan 7, 2021 at 20:28
Anycase - MariaDB does not implement JSON_TABLE function or similar - so you need in iterative CTE which will extract separate values from your JSON, then join 2nd table and use GROUP_CONCAT to reconstruct single CSV value which you need. Alternatively you may quote reports.id with " and search for it in identity.reprots_id. — Akina
– Akina, Commented Jan 7, 2021 at 20:28

Akina · Accepted Answer · 2021-01-07 20:32:55Z

1

Look for

SELECT i.id, i.identifier, GROUP_CONCAT(r.name)
FROM Identity i
JOIN Reports r ON LOCATE(CONCAT('"', r.id, '"'), i.report_ids)
GROUP BY i.id, i.identifier
-- WHERE JSON_VALID(i.report_ids)

answered Jan 7, 2021 at 20:32

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

geertjanvdk · Accepted Answer · 2021-01-23 18:06:31Z

Accepted answer is great. However, to complete, and since this question is tagged as 'mysql', I would like to add the solution with MySQL 8.0 (since this was very similar to another question).

As mentioned in the comments, JSON_TABLE can be used, and with JSON_ARRAYAGG you can directly proced JSON in the result:

SELECT i.id, i.identifier, JSON_ARRAYAGG(reports.name)
FROM identity as i
    JOIN JSON_TABLE(i.report_ids, '$[*]' COLUMNS (id INT PATH '$')) AS r 
    JOIN reports ON reports.id = r.id
GROUP BY i.id, i.id, i.identifier;

Result (was not sure which ID was wanted):

+----+------------+------------------------------------+
| id | identifier | JSON_ARRAYAGG(reports.name)        |
+----+------------+------------------------------------+
|  1 | TOP        | ["Report food", "Report Beverage"] |
+----+------------+------------------------------------+

Collectives™ on Stack Overflow

MySQL Query select concat on json field ids

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related