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

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

Exactly what I needed! Thank you!!

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

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

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

cool

This helped me enormously - thank you!

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.

Thank u kann as solution for the big data manipulation

How do you do this for oracle database?

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.

Does it works with postgres?

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

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?

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.

Very clear! 👍

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

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

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

I just listen "ok ok ok"

Will these functions work with SQLite?

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

I have being using FOR JSON religiously since it's release back in 2016 (I think). I do not use it for "json to table", rather, I use it for "table to json" in normal select queries. I think this feature is, by far, the best SQL feature that no one knows about. Thanks for making this video. Everyone should know about FOR JSON and use it - especially in the modern world of web development. Front-end frameworks like Angular and React are json-based (because they are javascript-based) and going from SQL to front-end was always a nightmare because SQL could never handle nested objects - until now!

I have written some very complex json queries that, in the past, would have been N number of queries to your database, where now it's just one query.

I use procedures to execute my json queries, and because FOR JSON returns a string, you must return that string like this in your procedure:

create procedure MyTestJson
(
-- parameters
)
as
begin
declare @result nvarchar(max)
set @result = (
-- for json select query
)
select @result;
end

If you do not return your result this way, your json string will be truncated.

Also, I highly recommend to not use FOR JSON AUTO. Learn how to implement FOR JSON PATH and only use that. You have a lot more control using PATH and AUTO can give you mixed results that you may not expect.

What SQL is this Oracle or DB/2?

Awesome, thanks!

ok ok okay

Which ide is this

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

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

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

Why am i getting NULL values..Please help

Ok

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

Very Helpful. Thanks

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

How to send SQL server data to json file

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

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

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

Thanks James

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

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

Okay?

Hi James,
Just on the same Context can we create an JSON output file from the MSSQL with suffix of DATE time<yyyymmddHHMMSS>. 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_<yyyymmddHHMMSS>

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?

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

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 '

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

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