How to read JSON files embedded in a list of lists?

[AmineHY]

Hello

I am trying to read this JSON file but didn't succeed

 

You can see the head of the file, JSON inside a list of lists. Any idea how to read this file?

[Debayan]

Hi @Amine HADJ-YOUCEF​ , The data sources are limited, please refer: https://spark.apache.org/docs/latest/sql-data-sources-json.html#data-source-option

Also, https://docs.databricks.com/external-data/json.html

Please let us know if this helps.

[AmineHY]

Thank you for sharing,

these links do not address the exact problem I am facing

[AmineHY]

For now I red the file as a Text and it gives :

it is the concatenation of multiple JSON files, that why the native JSON parser can't load the data.

The surprising thing is that it detects the right schema!

[AmineHY · Accepted solution]

Here is my solution, I am sure it can be optimized

import json
 data=[]
with open(path_to_json_file, 'r') as f:
   data.extend(json.load(f))
   df = spark.createDataFrame(data[0], schema=schema)

✌️

[adriennn]

The correct way to do this without using open, which will work only with local/mounted files is to read the files as binaryfile and then you will get the entire json string on each row, from there you can use from_json() and explode() to extract the objects inside your array (I assume the outer array is just wrapping the other array, if you have records inside an array of arrays, then you would use explode() twice.