topic Re: How to read JSON files embedded in a list of lists? in Data Engineering

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

AmineHY — Wed, 16 Nov 2022 13:24:01 GMT

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?

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

AmineHY — Mon, 21 Nov 2022 13:04:09 GMT

Thank you for sharing,

these links do not address the exact problem I am facing

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

AmineHY — Mon, 21 Nov 2022 13:06:09 GMT

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!

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

AmineHY — Mon, 21 Nov 2022 13:51:12 GMT

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)

✌️

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

Debayan — Fri, 18 Nov 2022 07:31:38 GMT

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.

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

adriennn — Fri, 13 Sep 2024 05:33:50 GMT

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.