Форматирование сложной структуры json для сгруппированных данных с помощью sql server

Question

Я новичок в функции SQL json.
Вот проблема: я хочу сгенерировать требуемую структуру JSON из данных таблицы ниже, используя функцию SQL-JSON.

Данные таблицы у меня есть:

Col1    | Col2   |  Col3  | Col4
--------------------------------
School  | Room   | Jon    | Present
School  | Room   | Hanna  |Absent
School  | Room   | Teena  | NA
School  | Hall   | Length | 12
School  | Hall   | Breath | 11
School  | Hall   | Heught | 4
School  | Ground | school | xuz
School  | Ground | col    | oo
School  | Ground | else   | a
College | ClassA | teacher| 2
College | ClassA | students|20
College | ClassA | others | 1
College | ClassB | Des    | 3
College | ClassB | tv     | 0

Требуемый формат данных JSON

{
    "School":{
    
        "Room":{
          "Jon":"Present",
          "Hanna":"Absent",
          "Teena":"NA"
        },
        "Hall":{
          "Length":"12",
          "Breath":"11",
          "Heught":"4"
        },
        "Ground":{
          "school":"xuz",
          "col":"oo",
          "else":"a"
        }   
        
    },
    "College":{
        "ClassA":{
          "teacher":"2",
          "students":"20",
          "others":"1"
        },
        "ClassB":{
          "Desk":"3",
          "tv":"0"
        }
    }
}

Мне нужно знать, как отформатировать данные в указанном выше необходимом формате json с помощью FOR JSON PATH

Answer 1

С Postgres вы можете использовать jsonb_object_agg() для достижения этой цели:

select jsonb_build_object(col1, jsonb_object_agg(col2, j1))
from (
  select col1, col2, jsonb_object_agg(col3, col4) as j1
  from t
  group by col1, col2
) t
group by col1;

Пример онлайн