query

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



Sharing buttons:

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