Creating Schema of JSON type and Reading it using Spark in Scala [Error : cannot resolve jsontostructs]

Question

I have a JSON file like below :

{"Codes":[{"CName":"012","CValue":"XYZ1234","CLevel":"0","msg":"","CType":"event"},{"CName":"013","CValue":"ABC1234","CLevel":"1","msg":"","CType":"event"}}

I wanted to create the schema for this and if the JSON file is empty({}) it should be an empty String.

However, df Output is below when I used df.show:

[[012, XYZ1234, 0, event, ], [013, ABC1234, 1, event, ]]

I created Schema like below :

val schemaF = ArrayType(
  StructType(
    Array(
      StructField("CName", StringType),
      StructField("CValue", StringType),
      StructField("CLevel", StringType),
      StructField("msg", StringType),
      StructField("CType", StringType)
    )
  )
)

When I tried below,

val df1 = df.withColumn("Codes",from_json('Codes, schemaF))

It gives AnalysisException :

org.apache.spark.sql.AnalysisException: cannot resolve 'jsontostructs(Codes)' due to data type mismatch: argument 1 requires string type, however, 'Codes' is of array<structCName:string,CValue:string,CLevel:string,CType:string,msg:string> type.;; 'Project [valid#51, jsontostructs(ArrayType(StructType(StructField(CName,StringType,true), StructField(CValue,StringType,true), StructField(CLevel,StringType,true), StructField(msg,StringType,true), StructField(CType,StringType,true)),true), Codes#8, Some(America/Bogota)) AS errorCodes#77]

Can someone please tell me why and how to resolve this issue?

Codes column is already of type array of struct, why do you want to use from_json? — blackbishop
– blackbishop, Commented Mar 22, 2021 at 18:15
I see that your Json file is not well defined, where is the closing ] for your your array — Nassereddine BELGHITH
– Nassereddine BELGHITH, Commented Mar 22, 2021 at 18:37
Because if the codes is empty(i.e { Codes : [] }), I want to make use of Schema @blackbishop — YOGESH S
– YOGESH S, Commented Mar 23, 2021 at 4:57

Nassereddine BELGHITH · Accepted Answer · 2021-03-22 19:17:28Z

0


val schema =
      StructType(
        Array(
          StructField("CName", StringType),
          StructField("CValue", StringType),
          StructField("CLevel", StringType),
          StructField("msg", StringType),
          StructField("CType", StringType)
        )

      )
    val df0 = spark.read.schema(schema).json("/path/to/data.json")

answered Mar 22, 2021 at 19:17

Nassereddine BELGHITH

6605 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

YOGESH S Over a year ago

It's still the same error when I tried your schema. org.apache.spark.sql.AnalysisException: cannot resolve 'jsontostructs(Codes)' due to data type mismatch: argument 1 requires string type, however, 'Codes' is of array<structCName:string,CValue:string,CLevel:string,CType:string,msg:string> type @itIsNaz

Nassereddine BELGHITH Over a year ago

I suppose that you have a column named Code in df in this case just add it : val df1 = df0.withColumn("Codes",f.from_json(f.col("Codes"), schema)) where f is defined as import org.apache.spark.sql.{functions => f}

blackbishop · Accepted Answer · 2021-03-23 17:52:12Z

0

Your schema does not correspond to the JSON file you're trying to read. It's missing the field Codes of array type, it should look like this :

val schema = StructType(
  Array(
    StructField(
      "Codes",
      ArrayType(
        StructType(
          Array(
            StructField("CLevel", StringType, true),
            StructField("CName", StringType, true),
            StructField("CType", StringType, true),
            StructField("CValue", StringType, true),
            StructField("msg", StringType, true)
          )
        ), true)
      ,true)
  )
)

And you want to apply it when reading the json not with from_json function :

val df = spark.read.schema(schema).json("path/to/json/file")

df.printSchema
//root
// |-- Codes: array (nullable = true)
// |    |-- element: struct (containsNull = true)
// |    |    |-- CLevel: string (nullable = true)
// |    |    |-- CName: string (nullable = true)
// |    |    |-- CType: string (nullable = true)
// |    |    |-- CValue: string (nullable = true)
// |    |    |-- msg: string (nullable = true)

EDIT:

For your comment question, you can use this schema definition:

val schema = StructType(
    Array(
      StructField(
        "Codes",
        ArrayType(
          StructType(
            Array(
              StructField("CLevel", StringType, true),
              StructField("CName", StringType, true),
              StructField("CType", StringType, true),
              StructField("CValue", StringType, true),
              StructField("msg", StringType, true)
            )
          ), true)
        ,true),
      StructField("lid", StructType(Array(StructField("idNo", StringType, true))), true)
    )
  )

edited Mar 23, 2021 at 17:52

answered Mar 23, 2021 at 10:47

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

4 Comments

YOGESH S Over a year ago

To this, how to I add on one more column schema. lid:struct -> idNo:string

blackbishop Over a year ago

@YOGESHS not sure I understand your question. how do you want to add a column?

YOGESH S Over a year ago

To this, how to I add on one more column schema. lid:struct -> idNo:string,, example : {"Codes":[{"CName":"012","CValue":"XYZ1234","CLevel":"0","msg":"","CType":"event"},{"CName":"013","CValue":"ABC1234","CLevel":"1","msg":"","CType":"event"}],lid :{"idNo": "1234"}}

blackbishop Over a year ago

@YOGESHS Please se the edited answer on how to add that field into the schema. If the question was on how to add that column to the dataframe then use: df.withColumn("lid", struct(lit("1234").as("idNo"))).

Collectives™ on Stack Overflow

Creating Schema of JSON type and Reading it using Spark in Scala [Error : cannot resolve jsontostructs]

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related