CODEWITHSUNDEEP
mysqljsonjoinconcatenation
MeisterC
MeisterC

Reputation: 25

MySQL Query select concat on json field ids

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 :)

Upvotes: 0

Views: 864

Answers (2)

geertjanvdk
geertjanvdk

Reputation: 3520

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"] |
+----+------------+------------------------------------+

Upvotes: 1

Akina
Akina

Reputation: 42632

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)

Upvotes: 1

Related Questions