0

I am looking for a solution for following:

enter image description here

Where I need a SQL query that get data for each user_id with all enrolment_value merged into a single row for the same user.

I am wondering if this is really possible or not.

I have tried Group by query but I get only 1 record not sure how to merge all the records for a user espacially under enrolment_value column.

Desired output: enter image description here

Mohsin A.
  • 98
  • 1
  • 5
  • 1
    What you're looking for is a JOIN and for that you need a relational table: https://stackoverflow.com/questions/260441/how-to-create-relationships-in-mysql – Kwright02 Dec 27 '22 at 23:24
  • 2
    Does this answer your question? [Aggregate function in MySQL - list (like LISTAGG in Oracle)](https://stackoverflow.com/questions/9456380/aggregate-function-in-mysql-list-like-listagg-in-oracle) – Bryan Dellinger Dec 28 '22 at 03:12

1 Answers1

1

If you're looking for a way to aggregate all enrolment_value per user_id from MySQL, you can do so using aggregate functions like JSON_ARRAYAGG() or GROUP_CONCAT().

For example, given the following data sample on the enrolments table:

id user_id enrolment_value
1 1 {"a": "value 1"}
2 2 {"a": "value 2"}
3 1 {"a": "value 3"}

When we use JSON_ARRAYAGG as follows:

SELECT
    user_id,
 JSON_ARRAYAGG(enrolment_value) AS enrolment_values
FROM enrolments
GROUP BY user_id;

We get:

user_id enrolment_values
1 [{"a": "value 1"},{"a": "value 3"}]
2 [{"a": "value 2"}]

Thanks to the GROUP BY clause, all rows with identical user_ids will be "squashed" into one row. But because we paired the clause to JSON_ARRAYAGG, all enrolment_values of the rows that will be squashed, will be "encoded" into a JSON array.

Now, with all enrolment_values of unique user_ids merged into one as JSON array, decoding said JSON should be pretty trivial to do in Laravel.

Christian
  • 553
  • 4
  • 16
  • NEXT STEP, extract JSON to coulmns However, I am having issues with JSON_EXTRACT on GROUP_CONCAT, getting following error: "The document root must not be followed by other values" using following in SELECT: JSON_EXTRACT(GROUP_CONCAT(`enrolment_value`), '$.schedule') AS 'schedule', – Mohsin A. Dec 28 '22 at 12:46
  • When I try to use JSON_ARRAYAGG its showing me NULL responce but no error. JSON_EXTRACT(JSON_ARRAYAGG(`enrolment_value`), '$.schedule') AS 'from_array' The reason could be that SQL is escaping double qoutes on JSON_ARRAYAGG values, i.e.: ["{\"schedule\":\"25 Hours\",\"employment_service\":\"Disability Employment Service (DES)\"}"] Any solution for this? Thanks – Mohsin A. Dec 28 '22 at 12:48
  • @MohsinA. You need to use `JSON_ARRAYAGG()` with `JSON_EXTRACT()`, not `GROUP_CONCAT()`. Then, query like so: `JSON_EXTRACT(JSON_ARRAYAGG(enrolment_value), '$[*].schedule') AS from_array`. Hope this helps! – Christian Dec 29 '22 at 00:39