Hey, Cloud Gurus.
Welcome to our lesson on shredding JSON.
In this lesson, we'll first go over what shredding is.
We'll then give a code example of shredding JSON,
and wrap everything up with a review.
When we talk about shredding JSON,
really we mean parsing JSON files;
extracting values from JSON documents.
When we do this, we persist it to a data store.
Oftentimes a relational database,
such as Azure SQL Database.
So we can do things like stream data fragments
from Internet of Things, or other sources,
shred that information, and place it in a tabular format.
Once it's in a table,
we can easily query the cumulative set
simply using T-SQL to extract insights
from this overall data.
To do this, we use the OPENJSON function,
and that is a table-valued function.
It parses the JSON texts,
and then returns objects and properties as rows and columns.
In other words, it gives us a row-set view
over the JSON document.
Sort of an overlay, if you will,
so that we can view it and work with it in a tabular format.
Let's take a look at some example code using this method.
Our goal here will be to load data from a JSON document
into a SQL table.
First, we declare a variable and place the JSON in it.
Here we're declaring the variable, at JSON,
and we have a few different levels of properties here.
We have student data broken down into Azure or AWS student,
and then a couple of students within each one
that have an ID, a name and a surname.
We then take that variable and use SELECT * INTO
to place it into a table; the JSON students table.
You can see that we're using the OPENJSON function,
and referencing that variable
to pull back the Azure students.
The WITH clause is where we define a schema.
And with each of these fields,
we're specifying which part of the JSON properties
to map it to.
Our last line here, entitled Azure,
is pulling back the entire original JSON text
just to have the original as a backup.
Once this has run, created, and loaded the table,
you can query it with transact-SQL
just like any other table.
And each of these properties will be broken down
into their own column.
By a way of review,
shredding JSON means parsing its data into columns.
You can utilize the OPENJSON function
in your FROM clauses and joins to access JSON data.
And once the JSON has been shredded,
it can be persisted to a data store
such as Azure SQL Database
by using regular T-SQL INSERT statements.
That's it for this lesson.
A simple concept that can become very complex
in real-world scenarios.
But if you understand these basics,
then you should be able to go about
your JSON shredding tasks.
Thanks for joining me.
And when you're ready, I'll see you in the next video.