Sinds SQL Server 2016 (dus ook SQL Server 2017 en Azure SQL databases) biedt Microsoft ondersteuning voor JSON. Feitelijk zijn het maar een paar Sql-functies toegevoegd, maar het opent een heel nieuwe wereld van mogelijkheden. Deze blog legt je uit hoe het werkt en welke interessante toepassingen dit biedt.


XML

XML werd (en wordt) veel gebruikt voor het opslaan van data in een tekstueel formaat. Het mooie van XML is dat het zelf omschrijvend is (de naam van de tags is meteen de omschrijving van wat er in staat), overal gebruikt kan worden (het is immers puur tekst) en het is schema-loos. Dat wil zeggen dat het ene element meer, minder, en/of andere inhoud kan bevatten dan andere elementen. Dit in tegenstelling tot SQL tabellen. Elk record in een SQL tabel heeft immers de zelfde kolommen.

Schema-loze data kon daarom als XML worden opgeslagen in een SQL database. We zien de laatste jaren dat XML steeds meer vervangen wordt door JSON (JavaScript Object Notation). JSON heeft de zelfde goede eigenschappen als XML, maar heeft als voordeel dat het een stuk compacter (en daarmee effectiever) is dan XML.

JSON in SQL

Met de komst van JSON ondersteuning in SQL Server kunnen we dus mee gaan in deze trend in de schema-loze, tekstuele JSON objecten. Niet alleen opslaan in de database, maar ook query’s op uitvoeren en aanpassen op doorvoeren.

In onderstaande afbeelding is alle nieuwe functionaliteit opgenomen. Zoals je ziet is het geen enorme hoeveelheid nieuwe functionaliteit die je onder de knie moet zien te krijgen, terwijl het wel enorm krachtig is. Laten we naar de 3 afzonderlijke onderdelen gaan kijken


FOR JSON

Met XML hadden we al de mogelijkheid om een query te eindigen met de statement "FOR XML” waardoor de resultset in XML formaat werd geretourneerd. Het zelfde gebeurt dus nu met JSON.

Hieronder zie je een "normale” query van de producten tabel met een INNER JOIN op de subcategory tabel. Let echter op de toevoeging "FOR JSON PATH”.


Het resultaat is hieronder (gedeeltelijk) te zien. Het "PATH” keyword, samen met de kolom-aliassen "Category.Name” en "Category.ID” heeft ervoor gezorgd dat het resultaat een array van product objecten is, met "Category” as child-object.

 

OPENJSON

Met "OPENJSON” doe je het tegenovergestelde van de "FOR JSON”. Een string met daarin een JSON object wordt geopend als een record set. Eventueel kun je hier alle TSQL statements vervolgens op los laten.


Door het toevoegen van een "WITH” statement kun je gedetailleerder bepalen welke gegevens je uit de JSON wilt gebruiken. In dit voorbeeld gebruik ik een variabele, maar uiteraard mag dat ook een parameter van een stored procedure zijn.


Build-in functions

Veruit het krachtigst zijn de 4 build-in functions die  we kunnen gebruiken. Deze kun je gebruiken op de data in je tabellen. De JSON zelf wordt "gewoon” opgeslagen in NVARCHAR kolommen. Puur door de functies te gebruiken wordt het interessant.
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.

Om de functies in actie te zien, maak ik eerst een voorbeeld tabel "IWUser”. Hierin staat een kolom "User” met daarin JSON objecten.

 

De ISJSON()functie is bijvoorbeeld te gebruiken als CHECK-contraint op een tabel. Stel je hebt een NVARCHAR kolom "User” en daarin wil je gebruiker-objecten opslaan. De gebruiker-objecten kunnen verschillende properties bevatten, maar het moet wel valide JSON zijn. Dan kun je een check constraint toevoegen zoals hieronder.


Met de JSON_VALUE() functie kun je een specifieke waarde uitlezen. Zoals de "JobDescription” property van het user object. Sommige objecten hebben een lege waarde, sommige objecten hebben de hele property niet, maar de query geeft een duidelijk resultaat


Waar de JSON_VALUE() functie 1 waarde terug geeft, kun je met JSON_QUERY() een Array, of een JSON object terug krijgen. Bijvoorbeeld het "Address” child-object van de user.


Overigens kun je de functies niet alleen gebruiken in de SELECT list, maar ook op andere plaatsen, zoals GROUP BY, ORDER BY, of de WHERE. (of combinaties hiervan)


Performance

Ik ben onder de indruk van de snelheid waarmee de query’s uitgevoerd worden. Hier kun je echt niet zelf tegenop parsen of "LIKE %” statements voor gebruiken. Het filteren gaat razend snel en betrouwbaar.

Indexen

Mocht je vaak op dezelfde data zoeken (of indirect door bijvoorbeeld JOINS te gebruiken met andere tabellen), dan kun je gebruik maken van indexen. Je kunt niet direct een Index aanmaken op een property van een JSON object, maar dat is eenvoudig op te lossen door een "persisted computed” kolom te maken. Op dergelijke kolommen kun je indexen plaatsen en/of (primary / foreign) keys plaatsen. Daarmee wordt het nog veel sneller en betrouwbaarder.


Inzetgebied

Wanneer kun je JSON gebruiken? Eigenlijk is het inzetgebied erg breed. Denk hierbij aan:
  • Je webapplicatie of service (WebAPI) werkt met JSON objecten. In plaats van het heen en weer parsen of serizaliseren, kun je de objecten rechtstreeks opslaan, ophalen en retourneren
  • Je objecten hebben geen, of zeer wisselend schema, waardoor het lastig is om ze op te slaan in een SQL tabel.
  • Door de normalisatie kunnen de JOINS erg complex (en traag) worden om uit tientallen tabellen data bij elkaar te zoeken om 1 entiteit op te bouwen.
  • Wanneer je eigenlijk een NoSQL oplossing nodig hebt, maar SQL Server wilt gebruiken (bijvoorbeeld omdat daar de rest van de data ook in is opgeslagen)
  • SQL Server functionaliteit biedt die andere (NoSQL) databases niet bieden.
  • Je gebruik wilt blijven maken van je TSQL kennis.
Een situatie waarbij je een SaaS oplossing ontwikkeld. Voor elke klant wil je een aparte database, maar je wilt niet schema’s van tienduizenden databases hoeven aan te passen wanneer je een extra kolom wilt toevoegen voor een bepaalde nieuwe feature. Met JSON is dat niet nodig.

SQL Training

Interesse in deze en andere nieuwe ontwikkelingen in SQL Server? Onze trainingen zorgen ervoor dat je de juiste kennis en vaardigheden opdoet om het maximale uit jouw werk te halen.

Afhankelijk van de versie waarmee je werkt, is de training Updating Your Skills to SQL Server 2016 of Updating Your Skills to SQL Server 2017 een perfecte manier om volledig up-to-date te blijven.

Of meer weten over SQL development? Dan is deze training iets voor jou: Training Developing SQL Databases. Wil je meer informatie over welke SQL training het beste past bij jouw leerdoel en functie? Neem dan vrijblijvend contact met ons op via 040-2323390 of stel je vraag direct in onze chat. Onze opleidingsadviseurs staan voor je klaar om je op weg te helpen naar de beste training.

Chat met ons