Home / Blog / Softwareontwikkeling / Ontdek de kracht van JSON documenten in SQL server databases
Sinds SQL Server 2016 heeft Microsoft functionaliteit toegevoegd die het werken met JSON data heel interessant maken. Feitelijk zijn er maar een paar functionaliteiten toegevoegd, maar ze openen een heel scala aan nieuwe mogelijkheden.
Voor de meesten zal JSON al bekend zijn, maar voor de volledigheid nog een korte introductie: JavaScript Object Notation, oftewel JSON, is een veelgebruikt formaat voor data-uitwisseling. Zoals de naam al doet vermoeden is JSON de manier waarop objecten in JavaScript genoteerd worden. In de laatste jaren is het echter uitgegroeid tot een standaard voor data-uitwisseling tussen systemen, ongeacht de programmeertaal die er gebruikt wordt.
JSON is een tekstueel formaat, dus “human readable”, en tevens zelf-omschrijvend. In die zin is het vergelijkbaar met XML, dat ook jarenlang de standaard is geweest voor data-uitwisseling. Echter, omdat JSON compacter is en in bepaalde gevallen makkelijker in gebruik (bijvoorbeeld als object in JavaScript) is de populariteit van JSON toegenomen ten gunste van XML.
Het is een kleine stap van data-uitwisseling naar data-opslag. Zoals gezegd is JSON feitelijk een stuk tekst. Databases kunnen uiteraard teksten opslaan, maar dat is niet ideaal wanneer je met die data wilt gaan werken, zoals zoeken of aanpassen van de data binnen de JSON objecten.
Laten we snel kijken wat SQL Databases te bieden hebben om de kracht van SQL Server te combineren met de voordelen van JSON.
SQL Server databases hebben een duidelijk schema. Van een tabel staat vast hoeveel kolommen ze hebben, wat de naam is van de kolommen en welk type data (getal, tekst, datum, enz.) in de kolommen geplaatst kan worden. JSON data is veel vrijer. Je kunt een lijst van objecten hebben waarbij het ene object andere eigenschappen (data) bevat dan het andere object.
Je ziet hierboven een lijst van werknemers, waarbij elke werknemer andere eigenschappen heeft. In JSON helemaal valide, maar in een SQL Database levert dit wel uitdagingen op. Daarom kan het interessant zijn om de JSON data direct in de database op te slaan, in plaats van om te zetten naar een tabellenstructuur met een vast schema.
Hieronder zie je een overzicht van de JSON ondersteuning in SQL Server en Azure SQL databases. De interessantste functionaliteit bevindt zich in het blauwe vlakje: de built-in functions, maar eerst kijken we naar FOR JSON en OPENJSON.
Door het toevoegen van “FOR JSON” aan een query wordt het resultaat niet in tabelvorm geleverd, maar als JSON array. Neem onderstaande query en let daarbij op de laatste regel.
De toevoeging “PATH” in combinatie met 2 kolom aliassen zorgt ervoor dat “Category” een child object is geworden van de producten.
OPENJSON is eigenlijk het tegenovergestelde van “FOR JSON”. Waar “FOR JSON” een tabelstructuur omzet in JSON, zorgt OPENJSON dat JSON juist als tabel wordt gerepresenteerd.
Door het toevoegen van een “WITH” statement kun je gedetailleerder bepalen welke gegevens je uit de JSON wilt gebruiken.
Er zijn 4 functies toegevoegd aan SQL databases. Deze 4 functies maken het mogelijk om heel effectief met JSON data te werken terwijl ze “gewoon” als platte tekst worden opgeslagen in een NVARCHAR kolom.
Ik zal deze 4 functies uitleggen aan de hand van een “Users” tabel, met daarin 1 kolom “User”. In dit veld zitten JSON objecten met allerlei gebruikersinformatie.
De ISJSON() functie kunnen we dan bijvoorbeeld gebruiken als CHECK-contraint op een tabel. De gebruiker-objecten kunnen verschillende eigenschappen bevatten, maar het moet wel een valide JSON document zijn. Dan kun je een check constraint toevoegen zoals hieronder.
Met de JSON_VALUE() functie kun je een specifieke waarde uitlezen. Zoals de “JobDescription” eigenschap van het user object. Sommige objecten hebben een lege waarde of sommige objecten hebben de hele property niet, maar de query geeft een duidelijk resultaat.
De JSON_QUERY functie lijkt erg op de JSON_VALUE functie, want beiden zijn te gebruiken om data uit het object op te vragen. Echter, de JSON_VALUE functie geeft 1 waarde terug (zoals de jobdescription), terwijl de JSON_QUERY functie een Object of een Array terug geeft. Dit kunnen we bijvoorbeeld gebruiken om het “Address” child-object van de user op te vragen.
JSON_MODIFY() stelt je instaat om aanpassingen te doen aan de JSON. In principe kun je het hele JSON object vervangen wanneer er een wijziging is op een bepaald object, maar in sommige gevallen wil je slechts 1 waarde aanpassen. Zeker wanneer je een aanpassingen wilt doen aan meerdere objecten, dan kan de JSON_MODIFY functie uitkomst bieden. Denk hierbij aan een “archief” vlag zetten op alle documenten van een bepaalde datum. Hieronder zie je hoe ik de JobDescription van 1 object aanpas.
De 4 JSON functies zijn in de SELECT lijst te gebruiken, maar ook op andere plekken in de query zoals de ORDER BY of de WHERE.
De snelheid waarmee queries worden uitgevoerd is behoorlijk hoog. Het is In ieder geval sneller en minder foutgevoelig dan zelf proberen de juiste gegevens eruit te krijgen met “LIKE %”. Echter, in bepaalde gevallen heb je de snelheid nodig die je alleen met 1 of meerdere indexen kan bereiken. Aangezien JSON als tekst wordt opgeslagen is het niet direct mogelijk om een index aan te maken op een bepaalde eigenschap van je JSON objecten, maar dat is goed op te lossen door gebruik te maken van “persisted computed” kolommen. Een computed column berekent zijn waarde door middel van een functie en dat kan dus ook een JSON_VALUE() functie zijn. Door deze computed column ook “Persisted” te maken wordt het resultaat van de functie ook opgeslagen in de tabel en daarmee is deze kolom te voorzien van indexen en (primary/foreign) Keys. In onderstaand scherm zie je naast de “User” kolom nog 2 persisted computed columns “Id” en “Email”.
Wanneer er een JSON object in de “User” kolom wordt geplaatst of wanneer de data in een JSON object verandert, zullen de ID en de Email kolom automatisch voorzien worden van de nieuwe of aangepaste data. Door het plaatsen van indexen op deze 2 kolommen is zoeken of joinen op deze eigenschappen van het JSON object super snel!
Er zijn diverse scenario’s waarin het werken met JSON in je database interessant kan zijn:
Zelf heb ik deze functionaliteit gebruikt in een SaaS scenario. Het product werd aan duizenden klanten aangeboden, maar we wilden graag elke klant in een aparte database onderbrengen in verband met security en onderhoud. Wanneer je duizenden databases hebt wordt het een enorme uitdaging om schemawijzigingen door te voeren, bijvoorbeeld wanneer je een extra kolom nodig hebt voor een nieuwe feature. Wanneer je gebruikt maakt van JSON heb je dit probleem niet. Nieuwe objecten krijgen in dat geval een extra property, maar de database zelf hoef je niet aan te passen.
Heeft u interesse in de mogelijkheden van JSON, SQL Server of Azure voor uw organisatie? Neem dan vooral contact met Christian op. Hij wil je er graag alles over vertellen.
We nemen contact met u op!
Senet Eindhoven Gestelsestraat 258 5654 AM Eindhoven Bekijk op kaart
KvK nummer: 17115078 Btw nummer: NL807989083B01
