JSON and SQL Tutorial - Convert a table to JSON and JSON to table

you know years ago the most popular way to exchange data would typically be either CSV or fixed width and then XML started gaining popularity well today it's JSON and so being able to convert from a table to JSON and JSON to a table is a tool that should be in every sequel developer's toolbox and that's what we're gonna be covering today all right so now JSON so first things first let's take a look at the JSON we're gonna be working with okay so I have a simple JSON file here okay it's just a customer's JSON file so you can see we have customers okay and then a list of a couple customers here okay so we have cust ID age typical things you might find so this is what we're gonna be working with okay so the first thing I'm going to do is I'm gonna show you how to turn this JSON into a table or a tabular format okay that's the first thing we're going to do now notice a couple things we have some nested values here okay so within our key values we have nested values here okay this demographics okay so I'll show you how to deal with that and then I'll also show you how to take a table and turn it into JSON okay so those are the two main things that we're going to be going over today so let's get started alright so now all I've done here is I've taken our JSON that we've already reviewed and I've just thrown it into a variable here okay so we're gonna use that as the basis of our query okay but now let me explain how this is gonna work okay now our array is going to be turned into our table our objects are going to be turned into our rows and our key values are going to be our cells okay in our table that gets created so now let's see how we can query this JSON so we're gonna start out with of course select no surprise there and we're gonna select star we're gonna select everything from our JSON okay from and now to query the JSON we're going to use the open JSON function okay and then the first parameter is going to be your JSON so we'll pass in our JSON variable that contains all of our JSON and our second parameter is going to be our starting value okay so what do we want to query right what array do we want to query we want to query our customers okay so we want to query our array of customer objects right ok now the next thing we do is we want to type with open parentheses and this is where we're going to define our schema and map it to our JSON so this is where we define our sequel schema so we'll type art we'll define our first field which is ID and that'll be an int okay now these values don't have to match the column names for JSON okay but we're going to map them to them so for example our first column we're gonna map is cust ID okay we're gonna map that into our ID sequel ID okay so to do that we just do space open quotes or single ticks and then this is where we type the path to our JSON field so dollar sign customer dot cust ID alright so you use these dot operators to define the path to your JSON so now we can do this come down here and let's say we want name to we can do the same thing with name and we'll call that a var car and again that maps to dollar sign so our starting where our route writing or contacts okay customer dot name okay now let's go back to our JSON and let's say we want to get a value from our demographics key here right now these are nested values so let me show you how we access them okay it's pretty simple so let's define our schema over here our household income field right well suppose that's the value we want to get so we'll call this HHI and again we'll just mark our 50 now to map that again we'll do customer dot demographics dot household income okay let's go ahead and run this and there you have it now you see we've queried our JSON and we've put it into table or tabular format okay but now the other thing I want to show you here let's go back and look at our JSON now you'll notice that our first customer does not have a date of birth field defined however our second customer does now JSON is very forgiving when it comes to missing fields so let's go ahead and add this and see what this looks like so now let's add that and why do bee and that is ad and that and our path is customer dot e OB o missing a comma here okay now let's run this and as you can see it ran just fine and our date of birth is null because it didn't exist for our first customer but it still runs fine and just gives us a null value okay so it's very forgiving with missing fields okay as you as you know if you work with JSON but now what if we wanted this field to be mandatory right what if we wanted to strictly enforce that this field is populated in order for this to run okay we can do is we can preface our date of birth with strict and now let's run this and you'll see it gives us a message and says property cannot be found on the specified JSON path alright so sticking with that same JSON example customers let's suppose we just want to query a specific customer and a specific value within our JSON how do we do that well we use another function called the JSON value function so let's go ahead and see what that looks like so we'll type select JSON value parentheses we'll enter our JSON right so we user enter our JSON parameter and now we'll enter the path to our JSON so now our starting point would be our if we're looking at our JSON let's look at it too right so we want to navigate to customers let's say we want customer to okay and we want I don't know maybe the name of customer two okay so let's take a look at how we do that so we want to start at customers and now we open brackets here and we enter the index of the customer you want so that would be it's starting with the zero with index so that we want our second customer so our first customer would be index zero our second customer will be index one so we want the second customer dot customer now we're navigating down the down the JSON tree now dot name now let's run this and there we have it we've selected our customer name from a specific customer in our JSON using the JSON value function alright so we've successfully query JSON we've queried specific values in JSON but now what if we want to turn a table into JSON okay so we've went from JSON to a table now we want to do the reverse we want to go from a table to JSON okay so looking at the example here we have a demo customers table it's the same data we've been working with before just customer data ID name age all that but now we want to turn this table into JSON okay so let's take a look at how we do this so we'll modify our query here to do this okay so now in order to do this we're gonna use for JSON okay so let's go ahead and do this so the cool thing about this is that we actually use the column names or aliases to define our JSON structure so let's suppose we want to select the ID the name and then maybe we will want to do like some sort of custom field for our JSON okay so with logic so let's do that so now we're going to create the same structure we've been working with okay so they'll be our array will be our customers array and then within that each customer object okay so let's do this so we'll type first we'll work on our ID so we'll type select the ID as now this is pretty cool this is where you define the jinx on tree okay so we'll do customer dot cost ID okay so we will use the same name that we've been using in our JSON okay so we'll change the ID name from ID to cost ID okay and then this is the JSON path within customer it's called cust IDs that is the key okay so now we'll do the same thing with our name or pipe name as and again customer dot name okay now let's do something a little different let's say we want to create a field or a flag that tells us if this customer is our target customer or not right if they're in our target demographic okay and we'll use age to do this so let's type a case statement so we'll type case when and we'll just say when their age is less than 50 that's our target group of customers okay or our target demographic so when aged then 50 then and we'll just have a yes/no flag then yes else no and we'll type as now let's say we want to nest this value okay let's say we want to nest this just like we had you know our customer JSON we want to nest this value within our demographics okay so let's do that we'll type customer dot demographics dot target okay now since we're creating nested values let's go ahead and add another value to this so we'll just add age to this so age has an again customer dot demographics dot age okay so now this is where we add our for JSON path okay and then comma and this is where what we need to add our root node okay or the root of our tree okay and this will be customers okay so now we'll run this and there we have it now we have our JSON we can double click that to open it and now we've transformed our table into JSON all right now the last thing I want to show you quickly is the way to determine if your JSON is valid or not so let's go ahead and copy this JSON and we'll declare a new variable here JSON and we'll just declare this as a bar car okay and then let's set it equal to our new JSON that we just created and now in order to do this you just need to use the is JSON function so we'll just type select is JSON and we'll throw our JSON variable in there and let's just run this and then we get one one meaning it is valid true okay now let's throw something in here to invalidate it and we should see a zero and we do zero alright so that'll do it please don't forget to Like subscribe and hit the bell and also check out James tech tips comm for more bi related content and thanks for watching you

Channel: James Oliver

Views: 116,301

Keywords: isjson, for json path, json_value, convert json to table, convert table to json, sql json tutorial, sql json examples, openjson, query json, sql json

Id: oIlgea8Kp2M

Length: 13min 35sec (815 seconds)

Published: Mon Apr 06 2020