json


How do I flatten JSON to CSV using USQL


I can get some data out using Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple, but I'm having trouble flattening the whole file.
Here's the file format I'm working with:
{
"SourceUrl":"http://www.unittest.org/test.html",
"Title":"Unit Test File",
"Guest":"Unit Test Guest",
"PublishDate":"2017-05-15T00:00:00",
"TranscriptionSections":[
{
"SectionStartTime":"00:00:03",
"Sentences":[
{
"Text":"Intro."
},
{
"Text":"Sentence one"
},
{
"Text":"Sentence two"
}
]
},
{
"SectionStartTime":"00:04:46",
"Sentences":[
{
"Text":"Sentence three"
},
{
"Text":"Sentence four"
}
]
}
],
"Categories":null
}
What I'm trying to get is a row per text (5 of them) including it's 'SectionStartTime' and all top level properties ('PublishDate', 'Guest'...).
So far I can get a row per 'SectionStartTime' using this:
USE econosphere;
REFERENCE ASSEMBLY [Newtonsoft.Json];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];
DECLARE #in string="adl://abc.azuredatalakestore.net/data/20170515UnitTest.json";
DECLARE #out
string="adl://abc.azuredatalakestore.net/processed/20170515UnitTest.csv";
#ep = EXTRACT
Title string,
SourceUrl string,
Guest string,
PublishDate DateTime,
TranscriptionSections string
FROM #in
USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor();
#epAndTransctripts =
SELECT Title,
SourceUrl,
Guest,
PublishDate,
Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(TranscriptionSections).Values AS TranscriptionSections_arr
FROM #ep;
#all =
SELECT
Title,
SourceUrl,
Guest,
PublishDate,
Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(sects)["SectionStartTime"] AS TranscriptionSectionTimes
FROM #epAndTransctripts
CROSS APPLY
EXPLODE(TranscriptionSections_arr) AS t(sects);
OUTPUT #all
TO #out
USING Outputters.Csv();

Related Links

Custom devise sign_up: json response after sign up
SVG datum as a JSON object
In the Grunt.js docs, there is a “this.filessrc” option, is it possible to do “this.filesDest”?
elasticsearch search for elements with specified ID example
Strings maintaining leading and trailing quotes from JSON when using Jerkson
How to get json request in spring controller?
How to restrict results by price in Google products search API
Comparing two json data in extjs
How to replace gwt json with my own json implementation
passing array of json objects from php to android
Spring Web MVC JSON generation
save functionality on a webpage that will present clicked xy points as text?
JSON data error in ColdFusion 9
Camel Route/ActiveMQ unmarshaling JSON and sending to methods
Can't fetching records from nested JSON objects in Sencha Touch Application
In extjs how to make validate json

Categories

HOME
tensorflow
cluster-computing
ember.js
admin-on-rest
meshlab
checkbox
jsviews
voip
ncurses
ocaml
translation
ubuntu-12.04
bibtex
value
rebol
user
schemacrawler
google-classroom
window
jboss-eap-7
spinnaker
game-physics
public-key-encryption
squarespace
ejs
ssms-2016
pyyaml
flat-file
esql
cocoa-touch
tarantool
header-files
piwik
scalajs-react
compare-and-swap
cell-array
roundcube
read-write
outsystems
geo
qtablewidget
gettext
service-locator
powershell-remoting
remote-server
nunit-3.0
meta-raspberrypi
fltk
ntfs-mft
twiki
viewgroup
microdata
excel-interop
jdbi
w3-total-cache
recycle-bin
proof
ti-basic
pinvoke
tofixed
fontconfig
cdk
jszip
unity3d-editor
communication-protocol
ingres
coremidi
libreadline
cfeclipse
manifest.mf
gameanalytics
radians
qregexp
jackson-databind
achievements
picking
anythingslider
cctray
jenkins-scriptler
vips
coypu
sparse-file
gray-code
apache-mina
gmaps4rails
temp-tables
ibmsbt
docopt
java-collections-api
funkload
cppdepend
late-static-binding
svcutil.exe
openkinect
logentries
modeshape
driver-signing
asp.net-mvc-scaffolding
sql-server-2012-web
dllexport
ruboto
visual-studio-2003
ng-pattern
magickwand
mvs
pys60
caliper
getstring
visual-web-gui
ou
opengl-es-lighting
noir
zend-form-element
pantheios
getresource
msn
external-assemblies
chdatastructures
data-entry
dotproject

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App