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

In this video I will show you how to convert JSON to a table using OPENJSON and from a table to JSON using FOR JSON PATH with t-sql. I will also cover querying specific JSON objects using JSON_VALUE as well as validating JSON using ISJSON.

[@Ferruccio_Guicciardi]

Thanks for sharing the most flexible way to define the JSON tree from the dataset source !

[@mahsapourshahmari8458]

I really like the way you say OKAY :) it makes it sound too easy to follow, like you get it? it is that simple :)

[@jaxamokoto2783]

Exactly what I needed! Thank you!!

[@javaguitarist]

This helped me enormously - thank you!

[@cubicle_monkey]

I FINALLY got this working! Thanks for the walk through. If you had to iterate this process through multiple files, what do you think would work best?

[@MrsHelepolis]

Hello james ,i'm using mongo atlas sample database to train myself. i have an array in json format "cast": [ "Charles Kayser", "John Ott" ] how do i transfer this array to table?

[@dannyanicamamasgo6482]

Thx Jame! one question, how to process when there is character special column "año" json? Like n'$."Dueño Data"

[@KukuFamily]

Hi Oliver , we get json as a single row data, is it possible to have separate json row for each record?

[@oussamaoussama6364]

Pardon my out of context question: can you please upload the back propagation video for the neural networks series? Great explanation by the way.

[@utkarshverma1832]

Hi James, Just on the same Context can we create an JSON output file from the MSSQL with suffix of DATE time. Can we do this? Can we insert the data into a table of SQL so that i can import it? How can i read a JSON file which has ABCD_XX_12345_

[@mehmetkaya4330]

Super!!! Thank you so much for this great tutorial

[@justdrawit3038]

Very clear! 👍

[@andynelson2340]

Awesome, thanks!

[@MrYeduguri]

Kindly explain various grid formatting options and document formatting options as well James. Thanks in advance!

[@sonaligund]

could you please guide how to create json from table data in oracle 11g. It would be helpful. Thanks.

[@barryreeves6916]

Very Helpful. Thanks

[@ppp.1334]

do we always have to paste whole json into the code?

[@paapaasare]

How would this work on data from a json API url?

[@hadireg]

👍👍 Thanks!

[@monikamalladi5603]

In JSON to table section using OPENJSON where can we assign the table name ?

[@voltairend]

Thanks James

[@dexter9459]

hey chief got the following error with this "Msg 319, Level 15, State 1, Line 27 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon." with your query directly from your google docs

[@programminglearner4652]

Can we convert JSON (data received from api) to MySQL table for cloning api data

[@Imaginativeone_DF]

Will these functions work with SQLite?

[@abp4110]

How do I add a fetched JSON api and get value of the returned value ?

[@workstephens3135]

Anyway you can provide as downloads the original .txt and .sql files you have entered above? I try to type exactly as you have it but still get errors. Msg 102, Level 15, State 1, Line 1 - Incorrect syntax near '{'.Msg 103, Level 15, State 4, Line 2, The identifier that starts with '

[@JamesOliver]

Here you go: drive.google.com/file/d/1LIeumrtwskcrAzSU194Mt3HyLjLCiYE_/view?usp=sharing

[@theilluminatimember8896]

Is is possible to cast values like this from a json *column* as well? I'm struggling with a Laravel project where I need to order rows by a value stored in a json column. The problem is the default Laravel query builder orders it like a string where as I need it ordered numerically. If it's possible to cast the values into a numeric datatype it would sort properly.

[@javaguitarist]

I had the same situation where the Qty number was a string in the json. If you use the WITH like he describes, you can cast it by just defining the type right there. WITH ( Qty DECIMAL(18,3) '$.qtyOrd' )

[@theilluminatimember8896]

​@@javaguitarist I ended up solving it like this: ```php public function scopeIncludeJsonProp(Builder $query, $attribute, $name = null) { $attribute = str_replace('->', '.', $attribute); $path = explode('.', $attribute); if (in_array($path[0], $this->jsonColumns)) { $jsonSelector = '$.' . implode(".", array_slice($path, 1)); $cast = $this->jsonCasts[$attribute] ?? $this->defaultJsonCast; return $query->selectRaw("cast(json_value(`$path[0]`, '$jsonSelector') as $cast) as `". (!empty($name) ? $name : $attribute) . "`"); } return $query; } ``` I've put it inside a global scope method so I can use it everywhere

[@theilluminatimember8896]

I also added model attributes allow me to cast each the json value automatically in case of date, char or decimal This all allowed me to include it sort and filter using sql which is SECONDS faster (from 2 seconds avg to 0.5 seconds avg)

[@sachin3941]

How do i convert a whole column having many jsons to a table format?

[@JamesOliver]

I'm planning a follow-up video showing more complex examples.

[@n.boukari6323]

Thank u kann as solution for the big data manipulation

[@hiphopheaven]

Does it works with postgres?

[@hasanmougharbel8030]

God bless your great efforts in this channel. I have a simple question to ask as a newbie sql learner. Could i manage to channel or extract all sql data generated from an accounting program (based on sql) to our new sql server. The reason that we are bounded with the graphical interface of this accounting software as we cant come up with any solutions regarding analytics and automation, unless we can tailor and rearrange this sql database based to our specific needs. Thanks for helping me.

[@javaguitarist]

That is a very large question and many possible answers that depend upon many factors in your environment, all of which are too involved to put into KZbin comments. I suggest you look at SSIS if you need to transform the data after copying it. But the simplest way to start is: In SSMS right click the source database. Open Tasks, then either Export Data or Copy Database. Do this on a test system! Good luck.

[@giaptiennbros]

Hi James, does it work if I work with Oracle ?

[@JamesOliver]

Joe G Unfortunately I have zero experience with Oracle believe it or not. It is a good question.

[@MrYeduguri]

Hi James, appreciate your efforts. Please make a video on "conditional metric embedding methods in microstrategy.

[@JamesOliver]

Thank you for the suggestion. I will add to my list of ideas for potential future videos.

[@MrYeduguri]

@@JamesOliver.. thanks James!!

[@renz82]

How do you do this for oracle database?

[@gauravdutta5486]

Hello Sir ., which db is this Do we have to do the same thing in postgres

[@JamesOliver]

SQL Server Express

[@codeflu]

I have created Web tool to generate tables from complex JSON effortlessly and run SQL queries for powerful data analysis

[@karlethapaxton7281]

cool

[@Kraver97]

Hi James, I am getting error: "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.". I read to put ";" before "WITH" but then I get: "Incorrect syntax near '('.". Please help me. :) (Error occur even with yours .txt drive.google.com/file/d/1LIeumrtwskcrAzSU194Mt3HyLjLCiYE_/view?usp=sharing).

[@Kraver97]

I find out that OPENJSON() function requires at least 130lv of Compatibility where my level is only 110 (I cannot change that). So I change my question to: Is there any other way to do what you did on video?

[@pratikpatra67]

Hello James, Why you've used N while setting the value in JSON

[@JamesOliver]

Just means Unicode. Not really anything major to focus on. ;)

[@pratikpatra67]

@@JamesOliver Thanks 🙂

[@gkri8390]

How to enrich json from column from another table

[@v300]

What SQL is this Oracle or DB/2?

[@JamesOliver]

T-sql ie sql server

[@jobsjobs9987]

Can you do this in SQL SERVER 2014? Using BUILT IN JSON components?

[@JamesOliver]

Pretty sure it started with 2012

[@tchpowdog]

I think it's 2016 and later docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

[@taricov4662]

Can anybody explains to me why the Dollar sign $ is used? I am new to SQL

[@DefriaManda]

The "$." denotes the root node, in the JSON object. The string value directly after "$." is the ROOT node.

[@gameisrigged6942]

Ok

[@tingholm]

counted 57 :) reminds me of Robyns from HIMYM drinking game. "But-ehmm"

[@sradhawebcreations]

How to send SQL server data to json file

[@JamesOliver]

Do you mean export to a file?

[@rossocorsa6577]

Okay?

[@gameisrigged6942]

Ok

[@tingholm]

counted 57 :) reminds me of Robyns from HIMYM drinking game. "But-ehmm"

[@peterkhumalo9837]

Why am i getting NULL values..Please help

[@javaguitarist]

Please learn how to ask questions properly. Put yourself in the position of the reader and have respect for the free time and effort you are asking for. You give absolutely no information about your query at all. We are looking at a blank slate, and you ask why am I getting null values. How would you expect anyone to help you? In a lot of technical forums you could be banned for wasting people's time just for asking a question that way. I'm not trying to be mean but am trying to help you save time, learn faster, and get help and not have your unanswered questions ticking off people who would otherwise be happy to help you.

[@FredyArg]

also, can you do a sample json creation query where there are over 50,000 records? its kind of cute with four or how ever many you are using.

[@JamesOliver]

Fred E yea this video was just about the concepts not necessarily specific implementations but will take the feedback into consideration.

[@FredyArg]

James Oliver ugh I am sorry, I didn’t mean for that to come out like that, yes thank you for making the video.

[@JamesOliver]

Fred E It’s all good. I appreciate you watching! :)

[@tchpowdog]

If you're concerned about performance, I've tested FOR JSON with millions of records and the performance difference is negligible. When going from table to json, all FOR JSON does is convert the query result to a serialized json format (a string) - this is not a resource heavy or time consuming task from what I've seen. Your queries still need to be properly structured and optimized, like normal. Test it - write a query that returns 50,000 records. Add subselects, joins, whatever you want. Run it. Then slap "for json path" on the end of it and run it again - this generally works with straigtforward queries. You'll have to alias your subselects.

[@mohammedameen6583]

Which ide is this

[@javaguitarist]

SQL Server Management Studio. It is a free download from Microsoft.

[@raunelperez9421]

I just listen "ok ok ok"

[@KimboH55]

ok ok okay

[@hyungtaecf]

This json looks bad. It doesn’t actually needs the “customers” and “customer” keys. It could be simplified as just a json array with key-value pairs of id and body.

[@Pollux70]

STOP saying ok after every sentence you're not a parrot, ok.