使用sql server在分组数据上格式化复杂的json结构

Question

我是SQL json功能的新手
问题是：我想使用SQL-json功能从下表数据生成所需的json结构。

我拥有的Tabel数据：

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"
        }
    }
}

我需要知道如何在FOR JSON PATH 的帮助下将数据格式化为上述给定的必需json格式

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;

在线示例