U bent hier: home » blog

      JSON in SQL 2016 (deel 1)

      een webapplicatie of webshop wordt in de meeste gevallen voorzien van gegevens uit externe bronnen aan de hand van webservices zoals bijvoorbeeld uit een ERP pakket (artikels, producten, voorraad, …).

      Tot voor kort was er heel wat programmeer werk nodig om die gegevens op te delen en op te slaan in de juiste database tabellen en velden. Maar meer en meer vinden datastandaarden ook hun weg in platformen zoals SQL server 2016. Quinten, onze database specialist, schreef een eerste blog over een recente update in dit veelgebruikte platform van Microsoft.

      Tijdswinst voor de programmeur, minder kans op fouten, meer mogelijkheden tot uitwisseling en ultiem een besparing en een beter project voor onze klanten.

      JSON is momenteel een van de meest gebruikte formaten om data uit te wisselen. Een veelgevraagde update op de Ms SQL site was ondersteuning voor JSON in SQL 2016, wat dan ook gebeurde. Hieronder vind je een overzicht van de voornaamste wijzigingen en nieuwigheden.

      Opslaan van JSON

      Externe systemen formatteren JSON als tekst. Zo wordt JSON ook in SQL opgeslagen als tekst in een nvarchar.

      JSON in SQL 2016

      Met de functie ISJSON kan er via een constraint een beperking op het veld voorzien worden, die ervoor zorgt dat enkel correct geformatteerde JSON tekst in het veld kan worden bewaard.

      JSON in SQL 2016

      Werken met Json in SQL 2016

      SQL 2016 voorziet de volgende mogelijkheden met JSON:

      • JSON analyseren
      • Queries uitvoeren op JSON-data
      • JSON-data transformeren tot een relationeel formaat
      • Exporteren van een query-resultaat naar JSON-tekst

      Hieronder vind je een overzicht van de belangrijkste ingebouwde functies aan de hand van voorbeelden.

      VOORBEELD 1:
      Variabele tekst met JSON-tekst

      JSON in SQL 2016

      Met de functies JSON_VALUE en JSON_QUERY worden waarden en objecten uit de JSON-tekst gehaald.

      JSON in SQL 2016

      • JSON_VALUE geeft een waarde (tekst, getal, ja/nee) als resultaat, die gevonden wordt met het JSON-pad als tweede parameter. Resultaat: Basic en Roeselare.
      • JSON_QUERY geeft een object of array als resultaat, dat gevonden wordt via het JSON-pad. Resultaat: ["Sport", "Water polo"]

      Met de OPENJSON functie kan een array aangesproken worden. Als resultaat worden de elementen weergegeven.

      JSON in SQL 2016

      Deze functie maakt gebruik van JSON-paden om de waarden of objecten aan te spreken in de JSON-tekst (gelijkaardig aan Javascript syntax):

      • '$' – verwijst naar een volledig JSON-object
      • '$.property1' – verwijst naar één property in het JSON-object
      • '$[4]' – verwijst naar het vijfde element in de JSON-array (net zoals in javascript starten indexen vanaf 0)
      • '$.property1.property2.array1[5].property3.array2[15].property4' – verwijst naar een complex geneste property in het object
      • '$.info. "first name"' – verwijst naar de first name property in het info object.

      Opgelet:

      • Als er speciale tekens in de sleutel voorkomen (spatie, dollarteken,…), moet de sleutel tussen dubbele aanhalingstekens staan.
      • Het dollarteken is vergelijkbaar met het root “/” teken in XPath syntax.

       

      VOORBEELD 2:
      Het gebruik van de JSON-functies en paden

      SELECT Id, FirstName, LastName,    
                    JSON_VALUE(InfoJSON, '$.info.”social security number”') as SSN,
                   JSON_QUERY(InfoJSON, '$.skills') as Skills
      FROM Person AS t
      WHERE ISJSON( InfoJSON ) > 0
      AND JSON_VALUE(InfoJSON, '$.Type') = 'Student'

      Het resultaat van bovenstaande query geeft het volgende weer:

      • de ‘Id’, ‘FirstName’, ‘LastName’ van de standaardkolommen van een tabel
      • ‘social security number’ (JSON_VALUE(InfoJSON, '$.info.”social security number”') as SSN) en een array van de ‘skills’ van de JSON-kolom (JSON_QUERY(InfoJSON, '$.skills') as Skills)
      • Enkel de rijen die correct geformatteerde JSON-tekst bevatten (WHERE ISJSON( InfoJSON ) > 0) en die van het type ‘student’ zijn (AND JSON_VALUE(InfoJSON, '$.Type') = 'Student'), worden getoond.

      JSON-waarden kunnen dus gebruikt worden in verschillende delen van de query, zoals order by, group by, where,…

      FOR JSON kan elk resultaat van een query omzetten naar JSON-tekst

      Bv. tabel met de postcodes en gemeentes:

      JSON in SQL 2016

      Resultaat:

      JSON in SQL 2016  

      Tot binnenkort voor deel 2 over indexering en relaties voor json in sql 2016.

      Lees meer van Quinten op Linkedin 

       

      Contacteer ons voor een openhartig gesprek

      27 februari 2017 - Quinten Neirynck, database specialist
      Tags :webshops, webapplicaties, technologie