Spark Scala: Access data inside struct which is inside of an array

Question

The schema looks like this

root
|-- orderitemlist: array (nullable = true)
| |-- element: struct (containsNull = true)
| | |-- internal-material-code: string (nullable = true)
| | |-- lot-number: string (nullable = true)
| | |-- packaging-item-code: string (nullable = true)
| | |-- packaging-item-code-type: string (nullable = true)

how do I access the values for internal-material-code and lot-number

on creating the dataframe I do this

df.withColumn("internalmaterialcode", col("orderitemlist")(0).getItem("internal-material-code"))

also

df.withColumn("internalmaterialcode", col("orderitemlist")(0)("internal-material-code"))

also as follows

df.withColumn("orderitemlistarray", explode(col("orderitemlist"))) 
.withColumn("internalmaterialcode", col("orderitemlistarray").getItem("internal-material-code"))

also as follows

df.withColumn("orderitemlistarray", explode(col("orderitemlist"))) 
.withColumn("internalmaterialcode", col("orderitemlistarray.internal-material-code"))

but it gives out null

I have seen similar looking schemas on stackoverflow questions but none of the answers were useful for me. Could someone answer it or direct me to the correct place.

You're looking for explode. Give that a google, lots of info out there. — Andrew
– Andrew, Commented Oct 2, 2019 at 2:08
I tried it as follows .withColumn("orderitemlistarray", explode(col("orderitemlist"))) .withColumn("internalmaterialcode", col("orderitemlistarray").getItem("internal-material-code")) also as follows .withColumn("orderitemlistarray", explode(col("orderitemlist"))) .withColumn("internalmaterialcode", col("orderitemlistarray.internal-material-code")) — raj247
– raj247, Commented Oct 2, 2019 at 2:20

notNull · Accepted Answer · 2019-10-02 02:59:11Z

3

After explode, select the newly created column and it will gives all the data from struct fields.

Example:

val va="""{
    "orderitemlist": [{
        "internal-material-code": "123",
        "lot-number": "vv",
        "packaging-item-code": "pp",
        "packaging-item-code-type": "ll"
    },{
        "internal-material-code": "234",
        "lot-number": "vv",
        "packaging-item-code": "pp",
        "packaging-item-code-type": "ll"
    }]
}"""

val df=spark.read.json(Seq(va).toDS).toDF

df.withColumn("arr",explode(col("orderitemlist"))).select("arr.*").show()

Result:

+----------------------+----------+-------------------+------------------------+
|internal-material-code|lot-number|packaging-item-code|packaging-item-code-type|
+----------------------+----------+-------------------+------------------------+
|                   123|        vv|                 pp|                      ll|
|                   234|        vv|                 pp|                      ll|
+----------------------+----------+-------------------+------------------------+

Now you will get all the columns from struct inside array..!!

answered Oct 2, 2019 at 2:59

notNull

31.8k4 gold badges41 silver badges58 bronze badges

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

2 Comments

Parvathirajan Natarajan Over a year ago

I'm getting this error -

org.apache.spark.sql.AnalysisException: cannot resolve 'explode(value)' due to data type mismatch: input to function explode should be array or map type, not struct

. I tried this df.withColumn("arr",explode(col("value"))).select("arr.*").show() Can you please help me?

Parvathirajan Natarajan Over a year ago

I just fixed it by converting it - df.withColumn("arr",explode(array(col("value")))).select("arr.*").show(false)

Sarath Chandra Vema · Accepted Answer · 2019-10-02 04:22:13Z

1

I went through the code block shared by you & it is working fine. Please go through my work here(as an extension to the earlier solution):

>>>df.withColumn("ves", $"orderitemlist.lot-number").show
+--------------------+--------+
|       orderitemlist|     ves|
+--------------------+--------+
|[[123, vv, pp, ll...|[vv, vv]|
+--------------------+--------+

>>>df.withColumn("vew", $"orderitemlist".getItem("lot-number")).show
+--------------------+--------+
|       orderitemlist|     vew|
+--------------------+--------+
|[[123, vv, pp, ll...|[vv, vv]|
+--------------------+--------+

answered Oct 2, 2019 at 4:22

Sarath Chandra Vema

8121 gold badge8 silver badges13 bronze badges

Collectives™ on Stack Overflow

Spark Scala: Access data inside struct which is inside of an array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related