<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/using-r-sp_execute_external_script-with-json/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2017/01/09/using-r-sp_execute_external_script-with-json/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>JSON has become part of the SQL Server in the same version as R. Both were very highly anticipated and awaited from the community.</p>
<p>JSON has very powerful statements for converting to and from JSON for storing into / from SQL Server engine (FOR JSON and JSON VALUE, etc).  And since it is gaining popularity for data exchange, I was curious to give it a try with R combination.</p>
<p>I will simply convert a system table into array using for json clause.</p>
<pre>SELECT top 10 object_id  FROM sys.objects FOR JSON AUTO;</pre>
<p>and it gives back the result:</p>
<pre>[{&quot;object_id&quot;:3},{&quot;object_id&quot;:5},{&quot;object_id&quot;:6},{&quot;object_id&quot;:7},{&quot;object_id&quot;:8},
{&quot;object_id&quot;:9},{&quot;object_id&quot;:17},{&quot;object_id&quot;:18},{&quot;object_id&quot;:19},{&quot;object_id&quot;:20}]</pre>
<p>And sp_execute_external_script query without JSON would look like:</p>
<pre>EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'OutputDataSet &lt;- InputDataSet'
      ,@input_data_1 = N'SELECT top 10 object_id  FROM sys.objects'
WITH RESULT SETS ((nr INT));</pre>
<p>Now, let’s suppose we want to use JSON result directly into T-SQL using sp_execute_external_script. Yes, imagine getting results from an API and you want to push the results immediately into R for analysis. Very straight-forward package in R is called <a target="_blank" rel="nofollow" href="https://cran.r-project.org/web/packages/jsonlite/index.html">jsonlite</a> (also available is <a target="_blank" rel="nofollow" href="https://cran.r-project.org/web/packages/rjson/index.html">rjson</a>). Query would be as following:</p>
<pre>EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'library(jsonlite)
                OutputDataSet &lt;- data.frame(fromJSON(InputDataSet))'
      ,@input_data_1 = N'SELECT top 10 object_id  FROM sys.objects FOR JSON AUTO'
WITH RESULT SETS ((nr INT));</pre>
<p>Nope!</p>
<pre><span>Msg 39004, Level 16, State 20, Line 15</span>
<span>A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.</span>
<span>Msg 39019, Level 16, State 1, Line 15</span>
<span>An external script error occurred: </span>
<span>Error: Argument 'txt' must be a JSON string, URL or file.</span></pre>
<p>So the argument ‘txt’ must be a JSON string, URL or file. Khm…very “useful” error message, but problem is, that data from T-SQL is stored and presented as data.frame to R environment (Launchpad), because the data type passed to R is array of objects. And would look something like:</p>
<p><img sizes="(max-width: 410px) 100vw, 410px" data-image-description="" data-orig-file="https://i1.wp.com/tomaztsql.files.wordpress.com/2017/01/2017-01-08-22_04_46-rstudio.png?resize=410%2C93&amp;ssl=1" data-orig-size="384,87" data-image-title="2017-01-08-22_04_46-rstudio" class="wp-image-3317" data-comments-opened="1" data-recalc-dims="1" data-attachment-id="3317" data-permalink="https://tomaztsql.wordpress.com/2017/01/09/using-r-sp_execute_external_script-with-json/2017-01-08-22_04_46-rstudio/#main" alt="2017-01-08-22_04_46-rstudio" data-medium-file="https://i1.wp.com/tomaztsql.files.wordpress.com/2017/01/2017-01-08-22_04_46-rstudio.png?resize=410%2C93&amp;ssl=1?w=300" data-large-file="https://i1.wp.com/tomaztsql.files.wordpress.com/2017/01/2017-01-08-22_04_46-rstudio.png?resize=410%2C93&amp;ssl=1?w=384" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://i1.wp.com/tomaztsql.files.wordpress.com/2017/01/2017-01-08-22_04_46-rstudio.png?resize=410%2C93&ssl=1"></p>
<p>Running this query in native (R) environment, we at least get the idea where and how to tackle the problem. So we need to convert the data.frame to a charaters using toJSON and as.character, so that the end T-SQL query would look like:</p>
<pre>EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'
                library(jsonlite)
                js &lt;- InputDataSet
                js2 &lt;- as.character(toJSON(js))
                OutputDataSet &lt;- data.frame(fromJSON(js2))'
      ,@input_data_1 = N'SELECT top 10 object_id  FROM sys.objects FOR JSON AUTO'
WITH RESULT SETS ((nr INT));</pre>
<p>Now we get the correct results (as if we would not used JSON):</p>
<p><img data-image-description="" data-orig-file="https://i0.wp.com/tomaztsql.files.wordpress.com/2017/01/2017-01-08-22_22_40-sqlquery7-sql-sicn-kastrun-wideworldimportersdw-spar_si01017988-60_-micr.png?w=456&amp;ssl=1" data-orig-size="91,246" data-image-title="2017-01-08-22_22_40-sqlquery7-sql-sicn-kastrun-wideworldimportersdw-spar_si01017988-60_-micr" data-comments-opened="1" class="wp-image-3327" data-recalc-dims="1" data-attachment-id="3327" data-permalink="https://tomaztsql.wordpress.com/2017/01/09/using-r-sp_execute_external_script-with-json/2017-01-08-22_22_40-sqlquery7-sql-sicn-kastrun-wideworldimportersdw-spar_si01017988-60_-micr/#main" alt="2017-01-08-22_22_40-sqlquery7-sql-sicn-kastrun-wideworldimportersdw-spar_si01017988-60_-micr" data-medium-file="https://i0.wp.com/tomaztsql.files.wordpress.com/2017/01/2017-01-08-22_22_40-sqlquery7-sql-sicn-kastrun-wideworldimportersdw-spar_si01017988-60_-micr.png?w=456&amp;ssl=1?w=91" data-large-file="https://i0.wp.com/tomaztsql.files.wordpress.com/2017/01/2017-01-08-22_22_40-sqlquery7-sql-sicn-kastrun-wideworldimportersdw-spar_si01017988-60_-micr.png?w=456&amp;ssl=1?w=91" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2017/01/2017-01-08-22_22_40-sqlquery7-sql-sicn-kastrun-wideworldimportersdw-spar_si01017988-60_-micr.png?w=456&ssl=1"></p>
<p>So R is ready for JSON and JSON is also ready for R.</p>
<p>Happy R+JSON+SQLing!</p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/3282/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/3282/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></p>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://tomaztsql.wordpress.com/2017/01/09/using-r-sp_execute_external_script-with-json/"> R – TomazTsql</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/MyRHW4YaR5U"></a></div>