topic Re: How would i retrieve data JSON data with namespaces using spark SQL? in Data Engineering

How would i retrieve data JSON data with namespaces using spark SQL?

rammy — Tue, 22 Nov 2022 06:17:34 GMT

File.json from the below code contains huge JSON data with each key containing namespace prefix(This JSON file converted from the XML file).

I could able to retrieve if JSON does not contain namespaces but what could be the approach to retrieve records/values if each key is with a namespace prefix?

jsondf = spark.read.json("<path>/file.json")
 
#jsondf.printSchema()
jsondf.createOrReplaceTempView("ramp")
 
elements = spark.sql("SELECT * FROM ramp")
elements.show()

Here,I wanted to retrieve records of w: document/w.body/w:p. I tried different ways but nothing is working. Any suggestions really helpful

Re: How would i retrieve data JSON data with namespaces using spark SQL?

Pat — Tue, 22 Nov 2022 11:45:00 GMT

HI @Ramesh Bathini ,

not sure what you have tried, but maybe you can try this way:

 select `w:document`.`w.body`.`w:p` from ramp

Re: How would i retrieve data JSON data with namespaces using spark SQL?

rammy — Tue, 22 Nov 2022 17:48:44 GMT

Thanks alot @Pat Sienkiewicz for your response. It works for me

Re: How would i retrieve data JSON data with namespaces using spark SQL?

SS2 — Tue, 29 Nov 2022 20:45:22 GMT

I case of struct you can use (.) For extracting the value