BLOG

Softwareontwikkeling

Ontdek de kracht van JSON documenten in SQL server databases

6 juli 2020

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.

JSON in 1 minuut

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.

Schemaless

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.

JSON

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.

JSON in SQL databases

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.

 

JSON

 

FOR JSON 

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. 

 

JSON

 

De toevoeging “PATH” in combinatie met 2 kolom aliassen zorgt ervoor dat “Category” een child object is geworden van de producten.

 

JSON

 

OPENJSON

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. 

 

JSON

 

Door het toevoegen van een “WITH” statement kun je gedetailleerder bepalen welke gegevens je uit de JSON wilt gebruiken.

 

JSON

 

Built-in functions

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.

ISJSON() Deze functie controleert of een string valide JSON bevat.
JSON_VALUE() Haalt 1 waarde uit de JSON, naar aanleiding van een opgegeven pad.
JSON_QUERY() Haalt een JSON object of een array uit de JSON.
JSON_MODIFY() Past de waarde van een property van de JSON aan.

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.

 

JSON

 

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.

 

JSON

 

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.

 

JSON

 

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

 

 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.

 

JSON

 

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.

 

JSON

 

Indexeren van de JSON data

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!

Interessante usecases voor het werken met JSON in je database

Er zijn diverse scenario’s waarin het werken met JSON in je database interessant kan zijn:

  • Een webapplicatie of service (WebAPI) werkt met JSON objecten. In plaats van het telkens parsen of serizaliseren, kun je de objecten rechtstreeks opslaan, ophalen en retourneren.
  • De objecten hebben geen of zeer wisselend schema, waardoor het lastig is om ze op te slaan in een SQL tabel.
  • Wanneer er eigenlijk een NoSQL oplossing nodig is, maar je SQL Server wilt gebruiken (bijvoorbeeld wanneer de rest van de data daar ook in is opgeslagen).
  • Normalisatie van data kan ertoe leiden dat JOINS erg complex (en traag) worden. Bijvoorbeeld wanneer je uit tientallen tabellen de data bij elkaar moet zoeken om 1 entiteit op te bouwen.
  • SQL Server biedt functionaliteit die andere (NoSQL) databases niet bieden.
  • Je hebt kennis van T-SQL en deze kennis wil je blijven gebruiken.

Gedegen ervaring met JSON in het Senet team

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.

Meer informatie over JSON, SQL Server, Azure?

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.

 

Interesse in een gesprek?

neem contact op met Christian Peeters

Laat uw gegevens achter

We nemen contact met u op!

Zie onze privacyverklaring.