如何格式化查询结果以在特定的json输出中显示结果

我正在尝试学习如何格式化sql查询的结果，以便将输出设置为json。我真的很感激如果有人能指导，因为我将产生不同类型的图表我的结果。
例如，一个图表需要具有以下json结构：

{
    xAxis: {
        type: 'product',
        data: ['Côte de Blaye', 'Thüringer Rostbratwurst', 'Mishi Kobe Niku', 'Sir Rodney's Marmalade', '"Carnarvon Tigers', 'Raclette Courdavault', 'Manjimup Dried Apples']
    },
    yAxis: {
        type: 'value'
    },
    series: [{
        data: [263, 123.7, 97, 934, 1290, 1330, 1320],
        type: 'line'
    }]
};

我正在使用northwind存储过程

SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC FOR JSON PATH

以上是我的结果

[
{"TenMostExpensiveProducts":"Côte de Blaye","UnitPrice":263.5000},
{"TenMostExpensiveProducts":"Thüringer Rostbratwurst","UnitPrice":123.7900},
{"TenMostExpensiveProducts":"Mishi Kobe Niku","UnitPrice":97.0000},
{"TenMostExpensiveProducts":"Sir Rodney's Marmalade","UnitPrice":81.0000},
{"TenMostExpensiveProducts":"Carnarvon Tigers","UnitPrice":62.5000},
{"TenMostExpensiveProducts":"Raclette Courdavault","UnitPrice":55.0000},
{"TenMostExpensiveProducts":"Manjimup Dried Apples","UnitPrice":53.0000},
{"TenMostExpensiveProducts":"Tarte au sucre","UnitPrice":49.3000},
{"TenMostExpensiveProducts":"Ipoh Coffee","UnitPrice":46.0000},
{"TenMostExpensiveProducts":"Rössle Sauerkraut","UnitPrice":45.6000}
]

我想把x轴画成产品，y轴单位价格画成上面解释的结构。如果有人能指导我如何用sql设计自己的结构，我将不胜感激。

在sql server中，用json生成对象数组相当简单，例如：adventureworks数据库中最昂贵的前10个产品。。。

select top 10 Name, ListPrice
from Production.Product
order by ListPrice desc
for json auto;

[
    {
        "Name": "Road-150 Red, 62", "ListPrice": 3578.2700
    },
    {
        "Name": "Road-150 Red, 44", "ListPrice": 3578.2700
    },
    {
        "Name": "Road-150 Red, 48", "ListPrice": 3578.2700
    },
    {
        "Name": "Road-150 Red, 52", "ListPrice": 3578.2700
    },
    {
        "Name": "Road-150 Red, 56", "ListPrice": 3578.2700
    },
    {
        "Name": "Mountain-100 Silver, 38", "ListPrice": 3399.9900
    },
    {
        "Name": "Mountain-100 Silver, 42", "ListPrice": 3399.9900
    },
    {
        "Name": "Mountain-100 Silver, 44", "ListPrice": 3399.9900
    },
    {
        "Name": "Mountain-100 Silver, 48", "ListPrice": 3399.9900
    },
    {
        "Name": "Mountain-100 Black, 38", "ListPrice": 3374.9900
    }
]

生成原始数组有点困难，并且依赖于组合之类的技巧 json_query() 与 stuff() 以及 for xml . stuff() 以及 for xml 通常用于构建分隔的数据列表，例如逗号分隔的数字列表。然后我们可以使用 json_query() 用于返回的原始值 for json ，例如：

with Products as (
  select top 10 Name, ListPrice
  from Production.Product
  order by ListPrice desc
)
select
  json_query(N'[' + stuff((
    select ',' + quotename(Name, '"')
    from Products for xml path('')
    ), 1, 1, '') + ']') as 'Names',

  json_query(N'[' + stuff((
    select ',' + cast(ListPrice as nvarchar(20))
    from Products for xml path('')
    ), 1, 1, '') + ']') as 'ListPrices'
for json path;

[
    {
        "Names": [
            "Road-150 Red, 62",
            "Road-150 Red, 44",
            "Road-150 Red, 48",
            "Road-150 Red, 52",
            "Road-150 Red, 56",
            "Mountain-100 Silver, 38",
            "Mountain-100 Silver, 42",
            "Mountain-100 Silver, 44",
            "Mountain-100 Silver, 48",
            "Mountain-100 Black, 38"
        ],
        "ListPrices": [
            3578.27,
            3578.27,
            3578.27,
            3578.27,
            3578.27,
            3399.99,
            3399.99,
            3399.99,
            3399.99,
            3374.99
        ]
    }
]

要获得所需的结构，请使用 for json path 以及 without_array_wrapper 移除周围的 [] 字符。。。

with Products as (
  select top 10 Name, ListPrice
  from Production.Product
  order by ListPrice desc
), series as (
  select
    json_query('[' + stuff((
        select ',' + cast(ListPrice as nvarchar(20))
        from Products for xml path('')
        ), 1, 1, '') + ']') as 'data',

    'line' as 'type'
)
select
  'product' as 'xAxis.type',

  json_query('[' + stuff((
    select ',' + quotename(Name, '"')
    from Products for xml path('')
    ), 1, 1, '') + ']') as 'xAxis.data',

  'value' as 'yAxis.type',

  (select * from series for json path) as 'series'
for json path, without_array_wrapper;

{
    "xAxis": {
        "type": "product",
        "data": [
            "Road-150 Red, 62",
            "Road-150 Red, 44",
            "Road-150 Red, 48",
            "Road-150 Red, 52",
            "Road-150 Red, 56",
            "Mountain-100 Silver, 38",
            "Mountain-100 Silver, 42",
            "Mountain-100 Silver, 44",
            "Mountain-100 Silver, 48",
            "Mountain-100 Black, 38"
        ]
    },
    "yAxis": {
        "type": "value"
    },
    "series": [
        {
            "data": [
                3578.27,
                3578.27,
                3578.27,
                3578.27,
                3578.27,
                3399.99,
                3399.99,
                3399.99,
                3399.99,
                3374.99
            ],
            "type": "line"
        }
    ]
}

请注意 series 作为对象数组生成，这是将其嵌入自己的cte的结果。

如何格式化查询结果以在特定的json输出中显示结果

1条答案

相关问题

热门标签

最新问答