SELECT FOR JSON

What is SELECT FOR JSON?

In almost every new Web application, JSON is the file format for data exchange. JSON is readable, clearer than XML, for example, and has a smaller data to noise ratio, which is why it is becoming increasingly common in the Web world. JSON stands for JavaScript Object Notation. Now – similar to the way it used to work with SELECT FOR XML – Microsoft has decided to support this new de facto standard format with its own output option.

How can you use SELECT FOR JSON?

SELECT FOR JSON gives you the ability to output submission results in JSON format. You can give new names to the entities in your output and also format your output a bit. To test this, let’s take a look at the two most expensive products in the Wide World Importers database:

SELECT TOP 2 
     si.StockItemID
    ,si.StockItemName
    ,si.UnitPrice
    ,sg.StockGroupName
FROM [Warehouse].[StockItems] AS si
LEFT JOIN [Warehouse].[StockItemStockGroups] as sisg
ON si.[StockItemID] = sisg.[StockItemID]
LEFT JOIN [Warehouse].[StockGroups] sg 
ON sg.[StockGroupID] = sisg.[StockGroupID]
ORDER BY si.[UnitPrice] DESC

As expected, this gives us a result set with two products in it.

If we wanted to pass them to a web service, for example, we could put them in JSON format as follows:

SELECT TOP 2 
     si.StockItemID
    ,si.StockItemName
    ,si.UnitPrice
    ,sg.StockGroupName
FROM [Warehouse].[StockItems] AS si
LEFT JOIN [Warehouse].[StockItemStockGroups] as sisg
ON si.[StockItemID] = sisg.[StockItemID]
LEFT JOIN [Warehouse].[StockGroups] sg 
ON sg.[StockGroupID] = sisg.[StockGroupID]
ORDER BY si.[UnitPrice] DESC
FOR JSON AUTO;

The result is a cell containing the following string:

[
    {
        "StockItemID": 215,
        "StockItemName": "Air cushion machine (Blue)",
        "UnitPrice": 1899,
        "sg": [
            {
                "StockGroupName": "Packaging Materials"
            }
        ]
    },
    {
        "StockItemID": 75,
        "StockItemName": "Ride on big wheel monster truck (Black) 1/12 scale",
        "UnitPrice": 345,
        "sg": [
            {
                "StockGroupName": "Novelty Items"
            }
        ]
    }
]

This is quite nice, but not 100{cd57dc5c024b062e80cf443c8eab5674adc0a8508c08485cd20648099fe1d76c} satisfying yet, as we might want to rename the individual entries and the ‘g’ might be a bit annoying. So let’s rewrite the survey a bit:

SELECT TOP 2 
     si.StockItemID as 'item.itemId'
    ,si.StockItemName as 'item.name'
    ,si.UnitPrice as 'item.price'
    ,sg.StockGroupName as 'itemgroup.name'
FROM [Warehouse].[StockItems] AS si
LEFT JOIN [Warehouse].[StockItemStockGroups] as sisg
ON si.[StockItemID] = sisg.[StockItemID]
LEFT JOIN [Warehouse].[StockGroups] sg 
ON sg.[StockGroupID] = sisg.[StockGroupID]
ORDER BY si.[UnitPrice] DESC
FOR JSON PATH;

In doing so, we overwrite the behaviour previously set to AUTO for naming the items and therefore choose FOR JSON PATH. The result is much more nicely formatted than the first automatic attempt:

[
    {
        "item": {
            "itemId": 215,
            "name": "Air cushion machine (Blue)",
            "price": 1899
        },
        "itemgroup": {
            "name": "Packaging Materials"
        }
    },
    {
        "item": {
            "itemId": 75,
            "name": "Ride on big wheel monster truck (Black) 1/12 scale",
            "price": 345
        },
        "itemgroup": {
            "name": "Novelty Items"
        }
    }
]

But now we want to rename the root of the document:

SELECT TOP 2 
     si.StockItemID as 'item.itemId'
    ,si.StockItemName as 'item.name'
    ,si.UnitPrice as 'item.price'
    ,sg.StockGroupName as 'itemgroup.name'
FROM [Warehouse].[StockItems] AS si
LEFT JOIN [Warehouse].[StockItemStockGroups] as sisg
ON si.[StockItemID] = sisg.[StockItemID]
LEFT JOIN [Warehouse].[StockGroups] sg 
ON sg.[StockGroupID] = sisg.[StockGroupID]
ORDER BY si.[UnitPrice] DESC
FOR JSON PATH, ROOT('topItems');

The result is as follows:

{
    "topItems": [
        {
            "item": {
                "itemId": 215,
                "name": "Air cushion machine (Blue)",
                "price": 1899
            },
            "itemgroup": {
                "name": "Packaging Materials"
            }
        },
        {
            "item": {
                "itemId": 75,
                "name": "Ride on big wheel monster truck (Black) 1/12 scale",
                "price": 345
            },
            "itemgroup": {
                "name": "Novelty Items"
            }
        }
    ]
}

So klappt’s dann auch mit dem Web-Entwickler…
Und wer altmodischer ist und noch XML-Ausgaben benötigt, ersetzt einfach das JSON durch ein XML und kann weiter dem Tag-Wahnsinn frönen.

That’s how it works with the web developer… And if you’re more old-fashioned and still need XML output, simply replace the ‘JSON’ with an ‘XML’ and you can continue to indulge in tag madness.

References

• Official Microsoft documentation