用sqlserver格式化分组数据上的复杂json结构

s6fujrry 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(464)

我对sqljson特性还不熟悉
问题是：我想使用sqljson特性从下表数据生成所需的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

sql sql-server sql-server-2016 sql-server-2017 for-json

来源：https://stackoverflow.com/questions/63061131/format-complex-json-structure-on-grouped-data-with-sql-server

1条答案

按热度按时间

nzkunb0c1#

对于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;

在线示例

赞(0）回复(0）举报 2021-07-24

我来回答

用sqlserver格式化分组数据上的复杂json结构

1条答案

相关问题

热门标签

最新问答