>> You're probably using JSON in a lot of your applications,
but did you know that it's super
supported in Azure SQL and SQL Server.
Learn all about it and see some cool demos and get
an inside look at a new private preview this week on Data Exposed.
Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed.
Today I have a special guest joining me,
a UC from the SQL Server and Azure SQL product group.
UC, thanks so much for joining us today.
>> Hello. Thanks, Anna. Thanks for inviting me.
>> It's super exciting to have you on the show.
You're going to be talking about
some existing stuff and some new stuff, I think,
all in the realm of JSON for Azure SQL and SQL Server.
I guess let's just get right into it.
I'm curious, can you use JSON with Azure SQL and SQL Server,
and do people use it,
and what did they use it for?
>> Great question. We can use JSON
today in SQL Server from 2016 version onwards.
It's also available in SQL database and SQL MI.
You basically storage and Docker
commands in regular string column.
Then we provide many functions
which are used to manipulate the JSON Docker commands.
You can also take your relational data
and present it as JSON to your applications.
It's just a very easy and natural way
of interacting with the data,
just like any other data.
>> Awesome. That sounds pretty cool.
I know you prepared some demos,
so I'd love to just learn more about it as we go through a demo.
>> Sure.
>> Maybe a good starting point is,
how do I store JSON in SQL and query it?
>> Yes. Great question.
We'll now look at a quick demo.
Basically what I have here is a relational table,
but I have a column here called order_info.
This has a regular nvarchar type of a particular length.
There is this function called ISJSON,
which basically says, hey,
is the string column containing a JSON Docker command,
and in this case I want to make sure it's a JSON object.
That's how your setup would look for a typical table.
Then inserting the JSON Docker command is very straight forward.
You basically insert it as a string in
your insert statement, and that's it.
You insert it like any other string data and you can see like,
for example, in my Azure Data Studio.
>> Cool. This seems pretty straightforward and easy to get set up.
What about changing items in the JSON objects? How do we do that?
>> Great. Now I have the same JSON Docker command here.
For this purpose, I've stored in variable here.
What we're going to do is
change the quantity in this JSON Docker command.
It's basically some modern basket information.
As you can see, you can change different properties,
like specific key value
or add a particular element at the end of the array and so on.
This is all done using this JSON modify function. Execute it.
You basically provide a Docker command,
you provide apart and the new value,
and that's about it.
It's very straightforward. In this example,
I appended a phone number to the JSON Docker command.
This is one way to modify in place.
>> Nice. That's pretty cool.
What else can I do with JSON?
>> Yes. Now one of the common things is okay,
you are in a relational database,
so you may want to actually query
individual properties out of the JSON Docker command,
and then write some other competitions on top of it.
Here is an example where I am extracting certain properties of
a JSON Docker command and one just showing it to an application.
The second query shows how you take those values,
and do some simple aggregations with it.
These are again straightforward operations.
You give the string column and basically a path,
which is something inside the JSON Docker command,
and that's about it.
As you can see, it's very simple
to manipulate it like a regular SQL table.
>> Got it. Then I guess going the other direction,
like you showed me how to aggregate in JSON.
What if I wanted to go from SQL to JSON?
Maybe my app is using things in a JSON format.
>> Let's look at that one.
In this example, I basically have a table.
First what I'm going to do is actually take
the JSON Docker command and shred it into some relational format.
As you can see here,
I'm taking out all the individual properties,
and storing it in a regular relational table.
Here the columns are all separated.
Now I may want to transform it back to JSON,
because my application wants a JSON Docker command.
That's how you would do it.
You call these functions like JSON_OBJECT and JSON_ARRAY.
This can be used to construct any complex shape.
Like in this example,
you get back the original JSON Docker command.
>> Nice. This helps me put the logic
unlike the database side versus the application side, right?
>> Yeah, exactly. Because there are lot of applications
which store the data in relational format,
but now many of the interactions with other services are
done using some JSON values.
In that case, you can still store it in relational format,
send the output as JSON or similarly get the input as
JSON and store it in relational or even store it as it is.
>> Cool. Thanks UC. I think I learned a lot about JSON.
I'm really excited to go get my hands on it.
I know you're going to put some links in
the description so people can
go learn more and even see this sample.
Of course, I have to ask anything
new coming or what are you all working on in the JSON space?
>> One of the common feedback we heard from customers is,
hey, is that a specific type for JSON?
As you saw in the demo,
you are storing in a regular string column.
One of the things we are working on is a new native JSON type.
Just like XML today in SQL Server and SQL DB,
we'll now have a JSON type.
You can store these Docker commands and adjacent type,
and the database engine optimizes
the JSON Docker command in a binary format and stores.
This is now available for private preview in SQL DB.
That is something we are excited for customers to
try it out and give us the feedback.
>> Cool, awesome. Are we going to share a link
in the description on how that
gets included in the private review?
>> Yeah.
>> Awesome. Cool. Great. Well, thanks so much, UC,
for coming on the show talking to us about JSON,
how it works really well in Azure SQL and SQL Server.
To our viewers, hope you like this episode.
Go ahead and leave as a common, like this episode.
Let us know what you think of the new JSON capabilities.
Check the links in the description for ways to learn more,
and get the latest in the new private preview.
We hope to see you next time on Data Exposed.
[MUSIC]