How to format query result to show result in a specific json output

I am trying to learn how to format my result from SQL query to have my output as JSON. I will really appreciate if someone could guide as I will generate my results in different types of charts.

For instance, one chart need to have the following JSON structure:

    {
    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'
    }]
};

and I am using Northwind stored procedure

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

With the above I have results as

[
{"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}
]

I want to plot x-axis as products and y-axis unit price as in the structure explained above. I will really appreciate if someone could guide how I can design my own structure in SQL.

Answer

In SQL Server it’s fairly simple to generate arrays of objects in JSON, e.g.: the top 10 most expensive products in the AdventureWorks database…

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

Generating raw arrays is a little more difficult and depends on tricks like combining json_query() with stuff() and for xml. stuff() and for xml is commonly used to build delimited lists of data, such as a comma-separated list of numbers. We can then use json_query() on that is used to return the raw values for for json, e.g.:

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

To get your desired structure builds on that by using for json path and without_array_wrapper to remove the surrounding [] characters…

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

Note that series is generated as an object array, which is the result of embedding it in its own CTE.