Как получить многомерный массив, сгруппированный по нескольким столбцам в PHP, из одной таблицы MySQL?

Во-первых, я бы порекомендовал вам придерживаться своего кода. Запустите простой SQL-запрос и обработайте результаты, сопоставив их в нужную структуру вложенного массива. У вас уже есть этот код, его легче отлаживать и легче изменить в будущем, если вам нужно его изменить.

Я реализовал то, что вы описываете, для создания вложенной структуры в SQL-запросе. Это было довольно сложно, а SQL-запрос был достаточно сложным, так что в будущем возникнет проблема сопровождения, если нам когда-нибудь понадобится изменить содержимое вложенной структуры.

Решение, которое я использовал в SQL-запросе, заключалось в использовании нескольких уровней производных подзапросов таблицы и генерации агрегированных результатов JSON на каждом уровне с использованием функций JSON. Это требует использования MySQL 5.7 или более поздней версии, поскольку эти функции JSON не реализованы в более ранних версиях MySQL.

Данные демо-теста:

create table if not exists mytable ( id int primary key, survey_type varchar(20), guid char(36), guid_type varchar(20), table_id  int, created_at datetime );
insert into mytable values 
(2 ,'TYPE 1','6C7251E3-2151-4754-A413-51899FAAF6C2','question','2','2022-03-20 16:14:09'), 
(3 ,'TYPE 1','EF5AFA93-C74D-4920-A13A-17A9B43239CD','question','3','2022-03-20 16:14:09'), 
(4 ,'TYPE 1','5C059148-94BE-4225-B5C2-551A81B65F16','question','4','2022-03-20 16:14:09'), 
(5 ,'TYPE 1','356B8A5C-1072-47A5-A508-D9BDCBA92CCC','answer','5','2022-03-20 16:14:09'), 
(6 ,'TYPE 1','E0CE4C26-7ABD-4162-9C8C-B4DD540AE268','answer','6','2022-03-20 16:14:09'), 
(7 ,'TYPE 1','BFBC50FC-892D-43E9-A235-D76E0D0BEF29','answer','7','2022-03-20 16:14:09'), 
(8 ,'TYPE 2','B9DCC5C1-CBFB-4589-98EF-4524F3958968','survey','8','2022-03-20 16:14:09'), 
(9 ,'TYPE 2','C98FBFF9-6FE3-414E-BB14-08EDC8281E66','survey','9','2022-03-20 16:14:09'), 
(10 ,'TYPE 2','8A780B6E-EAE0-47D6-9D05-F52B795AE617','question','10','2022-03-20 16:14:09'), 
(11 ,'TYPE 2','E3818D30-BB69-4F03-B56D-B31691F8007E','question','11','2022-03-20 16:14:09'), 
(12 ,'TYPE 2','24C81BEF-BFCE-4964-AB01-F3579251313D','answer','12','2022-03-20 16:14:09'), 
(13 ,'TYPE 3','59381701-AFBC-48F8-AECE-DB3702EE2B15','answer','13','2022-03-20 16:14:09'), 
(14 ,'TYPE 3','7F4AC694-74DC-4BEA-ACFB-D8F070769FEE','answer','14','2022-03-20 16:14:09'), 
(15 ,'TYPE 3','B5C405B9-BA7E-471A-87DD-B69D9757276F','survey','15','2022-03-20 16:14:09');

Пример запроса:

select json_pretty(json_objectagg(survey_type, g)) as j
from (
  select survey_type, json_objectagg(guid_type, t) as g
  from (
    select survey_type, guid_type, json_objectagg(table_id, guid) as t
    from mytable
    group by survey_type, guid_type
  ) as t
  group by survey_type
) as g;

Выход:

{
  "TYPE 1": {
    "answer": {
      "5": "356B8A5C-1072-47A5-A508-D9BDCBA92CCC",
      "6": "E0CE4C26-7ABD-4162-9C8C-B4DD540AE268",
      "7": "BFBC50FC-892D-43E9-A235-D76E0D0BEF29"
    },
    "question": {
      "2": "6C7251E3-2151-4754-A413-51899FAAF6C2",
      "3": "EF5AFA93-C74D-4920-A13A-17A9B43239CD",
      "4": "5C059148-94BE-4225-B5C2-551A81B65F16"
    }
  },
  "TYPE 2": {
    "answer": {
      "12": "24C81BEF-BFCE-4964-AB01-F3579251313D"
    },
    "survey": {
      "8": "B9DCC5C1-CBFB-4589-98EF-4524F3958968",
      "9": "C98FBFF9-6FE3-414E-BB14-08EDC8281E66"
    },
    "question": {
      "10": "8A780B6E-EAE0-47D6-9D05-F52B795AE617",
      "11": "E3818D30-BB69-4F03-B56D-B31691F8007E"
    }
  },
  "TYPE 3": {
    "answer": {
      "13": "59381701-AFBC-48F8-AECE-DB3702EE2B15",
      "14": "7F4AC694-74DC-4BEA-ACFB-D8F070769FEE"
    },
    "survey": {
      "15": "B5C405B9-BA7E-471A-87DD-B69D9757276F"
    }
  }
}

Загрузите этот результат в свое клиентское приложение. Это одна длинная строка, поэтому я надеюсь, что ваши данные не длиннее длины MySQL max_allowed_packet.

Преобразуйте строку в желаемый вложенный массив с помощью json_decode().