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?
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_
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.
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 RU-vid 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.
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
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 '
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.
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' )
@@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
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)
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.
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).
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?
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.
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.
