in this video we're going to go over the object construct function within snowflake
an object is a semi-structured data type it's a variant data type that if you're
working with snowflake you're likely very familiar with first
let's look at how to specify the keys and values directly
to do this we'll write select the function is object construct and in here
we'll separate them by commas the first part will be
the key so let's say key one then we'll have the value let's say a and then key two and then the
next one would be the value we run this we can see that's how it's constructed it took the first
input as the first key the second input as the value now i should note that if you were to do
let's say key 3 and then null it's going to interpret this as a key with a null value
it's as if nothing came through so it actually leaves that out you won't see that key 3 in there
speaking of the nulls let's talk about that for a second working with nulls
if we were to copy this let's keep everything the same let's say key 4 the value is a string
null again here we have the sql null we have null here and then we'll have
key five using the parse json function for null let's see how these three most specifically are
interpreted we would expect all these to come through but let's see what these nulls look like
again we're creating a variant object so if we look at this here key one and two came through
just fine key three is missing so again just like before it's a sql null here so it keeps it out
key 4 is a string of null so it returns it as a string but the parse json null is interpreting
this as a null value and brings it over so again the difference here is key 3 is as though quite
literally nothing was available for that key and therefore because it's a semi-structured it just
doesn't return anything it doesn't give us that key p4 is a string so we get it and then key 5
is interpreting the value as null it's parse json until we see it here but with no strings around it
ok so up until this point we've been hard coding specifically the keys that we want and what i want
to show you now is how we can infer the keys using from clause in this case let's say you have a
previous table that you're selecting from you can actually infer the keys and construct an object
using that table or cte let me show you so what i've just pasted in here
is from a previous video if you watch the video on generating random data in
snowflake this is where this comes from here we're just generating random data
except for the hard-coded value obviously it's an important message that we're hard coding over here
and so what we'll do here is let's create an object based on this table let's turn these
columns and rows into an object instead and the way we can do that is select
object construct and using the star from mock data which is the name of this cte let's run all
this and here we can see it formatted it into a bunch of variant objects and whereas before
we were specifically indicating what the keys were here this time it's inferring it from the names of
the columns that we gave we can see we gave these columns a name let's say random string and it's
inferring that here and giving the value so a quick way to turn a table into a variant object
now another way to use this star clause in this case we had a table but let's say we wanted to
code values only in this case instead of selecting from a table we're only going to provide values
so we can use the values function this is going to be different than the first one because here
we're providing keys and values directly into the function everything we put in here
are only values and not keys so let's say one two and then if we have another one that's two
then abc let's see what this looks like here we can see it turned each of these
values into objects and by default gives it column one column two because we didn't provide the keys
it's using the star function but it doesn't know what the column names are whereas before
we could infer it from the table so we're getting column one column two
one two and in the next one it's two abc now let's say we wanted to add another column here it will
do it again and add another record with the same format column one column two is
just going to infer it however with this one thing i should point out is if you try to do
a different structure of the values it's going to throw an error if we were to add another value
this is going to give us an issue it's saying inconsistent number of values in the values
clause if you're going to do it this way if you're gonna do two two two we can't do three
here this also has to match so we'd have to match this back up and then it'll work
the last feature of this i want to point out is using expressions within the construct argument
up until this point we've used the table we've hard-coded keys and values we've just done values
another subtlety that you can do here if you wanted and i'm going to break this up to give
us some room let's say key one we'll call this created at and comma so again it's going to be
very similar to this i'm just breaking it down into different lines instead of a hard coded
value we could say current timestamp comma so now again it's as if i'm just doing them
in a row this way but i'm breaking it out but a more interesting example could be let's say
table row count and what we could do here is actually put a separate select statement
within here we have to wrap it in parentheses and let's say we want to do select count from
and i'll just grab a table that i have here we'll say nba players
it should be returning the value of that expression and just to maybe throw another
one in here we could do row number and do another select we can do select row number over order by
we'll use one of the sequence numbers we mentioned in another video and we'll close
this out we'll leave it at this here let's see what this looks like if we run this we can see
it gave us the created at row number then the table row count is five that means it ran that
select statement and returned the row count within this object so i think that's pretty cool it gives
you the ability to create a little bit more of a dynamic object if you really wanted to
that'll do it for this video i really wanted to show you how the object construct function works
and hopefully you can find some cool ways to use it on your end thanks for watching