SELECT FOR JSON

Was tut SELECT FOR JSON?

In nahezu jeder neuen Web-Anwendungen ist JSON das Dateiformat für Datenaustausch. JSON ist lesbar, übersichtlicher als beispielsweise XML und hat kleinere Datenmengen, deshalb findet es zunehmend Verbreitung in der Web-Welt. JSON steht für JavaScript Object Notation. Nun hat – ähnlich wie das früher mit SELECT FOR XML ging – Microsoft sich entschieden, dieses neue De Facto Standardformat mit einer eigenen Ausgabemöglichkeit zu unterstützen.

Wie könnt ihr SELECT FOR JSON verwenden?

SELECT FOR JSON gibt euch die Möglichkeit, Abgabeergebnisse im JSON-Format auszugeben. Dabei könnt ihr den Entitäten in eurer Ausgabe neue Namen geben und eure Ausgabe auch ein wenig formatieren.
Um das zu testen, nehmen wir uns mal in der Wide World Importers Datenbank die zwei teuersten Produkte vor:

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

Das liefert uns erwartungsgemäß eine Ergebnismenge mit zwei Produkten darin:

Wenn wir diese nun beispielsweise an einen Webservice übergeben wollten, könnten wir sie dafür wie folgt ins JSON-Format bringen:

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;

Das Resultat ist eine Zelle, die folgenden String enthält:

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

Das ist schonmal ganz schön, allerdings noch nicht 100{cd57dc5c024b062e80cf443c8eab5674adc0a8508c08485cd20648099fe1d76c}ig befriedigend, da wir die einzelnen Einträge möglicherweise noch umbenennen möchten und auch das sg ein wenig stört. Schreiben wir die Umfrage also ein wenig um:

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;

Dabei überschreiben wir das vorher auf AUTO gesetzte Verhalten zur Benennung der Items und wählen deshalb FOR JSON PATH. Das Resultat ist schon weitaus schöner formatiert als der erste automatische Versuch:

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

Doch nun möchten wir noch die Root des Dokuments umbenennen:

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');

Das Resultat ist folgendes:

{
    "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.

Referenzen

• Offizielle Dokumentation von Microsoft