in the modern world of data using the data type of json is absolutely critical especially when
you're working with big data sets and in this video we'll look at the ways that
snowflake allows you to work with this data type and hopefully make your life much easier
one of the most common things you'll run into is arrays so that's the first
thing we'll look at snowflake allows you to parse through json arrays directly by simply
traversing through the object in our case our column name is json we're calling from this
table and we're saying right here root the object in the array obviously starts at zero
meaning the first one and there is only one root object so zero then the children which is another
array and we're saying give me zero which is the first one and the name values we would expect to
see jane here and then in this one over here we're saying cities lived and the first one in
that object which would be the place which would be seattle so let's see what happens
and that's what we get we get jane in seattle just like we expected
you'll notice a difference here this result has quotations this one does not one tip that you can
do to to clean up your json results is to cast it as a particular data type you can do this across
snowflake in different places but i think when working with json specifically it's really helpful
so by adding this here gets rid of those quotations and lets snowflake know that this
is expected to be a string if we do the same here we now see the same happened here as well
next up we're going to look at a useful function that is the flatten function in snowflake
and what this will do is separate these events into individual json objects while preserving
the global data it returns a row for each object we're going to get a new row for every object in
an array and the syntax for for joining this onto your original table this is the table that we have
with the json object right if we add a comma and then use the term table and we're joining another
table that is going to be that flattened result set so we're taking that flattened out version
of the json object saying this is a table and we want to have that table attached to this
original data set the term value here will give us the result of of this table
and really all it's going to do as we can see here is put us down one level from from where this is
so if we look at the original this is you know root and everything within it
and now the value of this is essentially the same thing just without the root object so we've moved
down one level and in a second here we'll see how that can be useful in other ways now just like up
here where we extracted the value from the object with colon and parse it through here we can do a
similar thing here we're saying value which is the result of this flattened join give me full name so
we're looking in our details and saying in here give me full name give me this value and because
this is flattened out it will do this for every single record that it sees so we should expect
the value john doe mike jones and i think there's one more and anna so we should see those three
and here we do now this is repeated the actual json value but we were able to identify those
individual values by flattening that object and then parsing out the value we could do
something like this cast it as a string and give it an alias let's see see how this looks there
we go so now we have some data that we can work with that we've parsed out through a json object
even though we're only going down one level as we just saw with the flattening you can
add another one again based on that previously flattened value in this case our first level
got rid of the roots so we're just looking at everything here you know everything below it
if we say flatten the value of children that is going to be every value of this
for each record so we have this one we'll have these and i think that's it so we would expect
to see all of those results in terms of this value so in before there was only one value because we
only had one flattened table here we have two we've aliased them and we can get that value
here and we should see it completely laid out for us and here we go so this is the original
level that we've been working with and then the children list just flattened out there for us
you know if this was all we were concerned with we could do something like this then
maybe even parse out the name there we go next up we'll look at the parse json function
okay so parse json is very subtle and essentially it's telling snowflake that we want the actual
value in the json object so if you remember most of the objects were returning with those
quotations we were doing that string cast workaround to get that value what we can do
instead is say parse json and then the value so let's look what happens here if we do something
without the parse json and with it so here right away even though this is an integer
the original one here is being wrapped in the quotations whereas parse json it picked it out
an important difference is when you start to work with nulls and nulls in json can be
slightly different than in other areas if we have a value that is this in your json object
that will return as null in snowflake here this would return as null
and as would something that had no value if there was no value whatsoever
similar to in this case there is no value for children for the third option which is anna it's
null and that is returning as null this is don't worry about this one this is bringing in the other
table which we don't actually care about here not related to this example but the main point is that
if you know you had a json object that says something like name and then a value of null
like this within these that would be returned as a null here as well through parse json it would it
would recognize that that's what that's supposed to be as would if there was nothing that part's
a bit tricky but just something to keep an eye on as you start to work with parse json more in depth
and finally there is the get path function and get path is essentially going to return the value
of whatever path you put in so in this case we're looking at our our sample here we're looking
here and saying give me the value of root zero full name which would be
root first object and the first full name john doe and then gender so we would expect to see john
doe mail return because we're giving that path here and i'm just going to filter out fluff here
and here we go john doe male just like we expected because it's looking
at this path you know this is expecting valid json in this object right here but if it is you
can get a value by simply putting it in like that i guess there are certain scenarios where
you would want to do that i think using some of these other functions is easier but to each
their own and i'm sure there's different scenarios where that might make more sense
that'll do it for this video i wanted to do a brief overview on how you can
use json in snowflake because if you're a full-time engineer or anyone working with
significant data sets you're definitely going to run into this and it's important to know
thanks as always for watching please feel free to like subscribe
comment if you have any questions and i'll see you at the next video thanks