How to get values from nested json array using spark?

Question

I have this array

val myJson = {
"record": {
"recordId": 100,
"name": "xyz",
"version": "1.1",
"input": [
  {
    "format": "Database",
    "type": "Oracle",
    "connectionStringId": "212",
    "connectionString": "ksfksfklsdflk",
    "schemaName": "schema1",
    "databaseName": "db1",
    "tables": [
      {
        "table_name":"one"
      }
      {
        "table_name":"two"
      }
    ]
  }
]    
}
}

I am using this code to get this json in dataframe

val df = sparkSession.read.json(myjson)

I want values of schemaName & databaseName, how can i get them?

val schemaName = df.select("record.input.schemaName") //not working

Someone, please help me

You can't read json this way, you have to load it from file or create dataset. val df = spark.read.json(Seq(myJson).toDS()). Your "input" is an array so you should pass required index, for example df.select("record.input[0].schemaName") — chlebek
– chlebek, Commented Feb 11, 2021 at 10:03
I am reading it from file only, sorry df.select("record.input[0].schemaName") --> this i already tried, doesn't work — yog
– yog, Commented Feb 11, 2021 at 10:05

blackbishop · Accepted Answer · 2021-02-11 10:08:16Z

1

You need to explode the array column record.input then select the fields you want :

df.select(explode(col("record.input")).as("inputs"))
  .select("inputs.schemaName", "inputs.databaseName")
  .show

//+----------+------------+
//|schemaName|databaseName|
//+----------+------------+
//|   schema1|         db1|
//+----------+------------+

answered Feb 11, 2021 at 10:08

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

How to get values from nested json array using spark?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related