cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Importing irregularly formatted json files

MikeJohnsonZa
New Contributor

Hi

I'm importing a large collection of json files, the problem is that they are not what I would expect a well-formatted json file to be (although probably still valid), each file consists of only a single record that looks something like this (this is just an abstraction)

[{"name":"myName","Surname":"MySurname"},[{"address":"1","Type":"Home"},{"address":"2","Type":"Home"}],[{"Tel":"1"},{"Tel":"2"}]]

I would ideally like to import it using the standard json read option bus can't figure out how to structure the schema.

My first approach involved creating a UDF that imported the record as a string and returned a new properly formatted object

def structure(object):

  structure = {}

  structure["name"] = object[0]["name"]

  structure["Surname"] = object[0]["Surname"]

  structure["addresses"] = []

  structure["telephones"] = []

  for address in object[1]:

    structure["addresses"].append({"address": address["address"],"Type": address["Type"]})

  for telephones in object[2]:

    structure["telephones"].append({"Tel": telephones["Tel"]})

  return structure

This works but it will be slower and less intuitive.

using a schema like this "works" but the elements in the ID'd come back as null

mySchema = StructType([

          StructField("Name", StringType(), True),

          StructField("Surname", StringType(), True),

          StrutType(StructField("IDs",

            StructType([StructField("ID", StringType(), True)])

          )

             

      )

   ])

1 ACCEPTED SOLUTION

Accepted Solutions

jose_gonzalez
Databricks Employee
Databricks Employee

Hi @Michael Johnsonโ€‹,

I would like to share the following notebook which contains examples on how to process complex data types, like JSON. Please check the following link and let us know if you still need help https://docs.databricks.com/optimizations/complex-types.html

View solution in original post

3 REPLIES 3

Debayan
Databricks Employee
Databricks Employee

Hi, when you are mentioning irregularly formatted json files, does the source look like this? or when it is pasted it looks like json format is bad. Please explain it a little more.

Anonymous
Not applicable

Hi @Michael Johnsonโ€‹ 

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!

jose_gonzalez
Databricks Employee
Databricks Employee

Hi @Michael Johnsonโ€‹,

I would like to share the following notebook which contains examples on how to process complex data types, like JSON. Please check the following link and let us know if you still need help https://docs.databricks.com/optimizations/complex-types.html

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group