JSON uitlezen in Snowflake

Eerder hebben we in dit blogartikel het gehad over de mogelijkheid om een JSON bestand te uploaden richting Snowflake, en op te slaan in een enkele kolom en zelfs in een enkele rij.

Nu gaan we kijken hoe we deze data dan vervolgens weer uit de JSON kunnen halen en netjes kunnen weergeven in een tabel.

Als we kijken naar ons begin punt , dan hebben we de volgende JSON in Snowflake staan :

{
  "Roosterplanning": [
    {
      "employmentPersonNumber": "123456/1",
      "resourceBeginDate": "2020-02-01",
      "resourceEndDate": "2020-07-31",
      "resourceFunctions": "Algemeen",
      "resourceId": "8269",
      "resourceName": "Johanna"
    },
    {
      "employmentPersonNumber": "123457/1",
      "resourceBeginDate": "2020-01-01",
      "resourceEndDate": "",
      "resourceFunctions": "Niveau 1",
      "resourceId": "8267",
      "resourceName": "Sasha"
    },
    {
      "employmentPersonNumber": "123458/1",
      "resourceBeginDate": "2020-02-01",
      "resourceEndDate": "2020-07-31",
      "resourceFunctions": "Algemeen",
      "resourceId": "8268",
      "resourceName": "Judith"
    }
  ]
}

Het beeld in Snowflake :

We kunnen dus duidelijk de structuur zien , opgeslagen als een overzichtelijke lijst. Nu is het voor ons de laatste stap om deze JSON uit elkaar te halen.

We gebruiken hiervoor standaard SQL die niet heel veel spannender is dan normaal , met 1 uitzondering :

SELECT 

    VALUE:employmentPersonNumber::VARCHAR AS personeel_id_increment,
    VALUE:resourceBeginDate::DATE AS roosterplanning_startdatum,
    TO_DATE(NULLIF(VALUE:resourceEndDate::VARCHAR,'')) AS roosterplanning_einddatum,
    VALUE:resourceFunctions::VARCHAR AS personeel_functie,
    VALUE:resourceId::INT AS roosterplannig_id,
    VALUE:resourceName::VARCHAR AS personeel_naam
    
FROM   JSON_TEST
, LATERAL FLATTEN( INPUT => JSON:Roosterplanning)

De structuur voor het uitpluizen is hierin altijd dezelfde :

VALUE:<elementnaam>::<datatype> AS <tabelnaam>
De laatste AS is optioneel maar wel iets wat aan te raden is met betrekking tot leesbaarheid.

De uitzondering is te zien op het einde bij de FROM statement:
FROM JSON_TEST , LATERAL FLATTEN( INPUT => JSON:Roosterplanning)
Er gebeuren hier 2 stappen :
Flatten, returned een nieuwe regel voor elk object.
Lateral , een Lateral join , deze joined de data met alle informatie die buiten het object zelf zit , in ons geval is dat dus Roosterplanning gejoined op de overige kolommen

Het resultaat is een bruikbare tabel die ook weer door de standaard SQL kan worden benaderd!

De algemene JSON output uit een hoop systemen is dus niet langer een nachtmerrie om uit te pluizen en in een database te krijgen door de mogelijkheden van Snowflake en de VARIANT kolom!