Filter based on JSON data which is in a string column in a Spark dataframe

Question

I have a Spark dataframe in the below format, where FamilyDetails column is a string field:

root
 |-- FirstName: string (nullable = true)
 |-- LastName: string (nullable = true)
 |-- FamilyDetails: string (nullable = true)

+----------+---------+--------------------------------------------------------------------------------------------------------------------------+
|FirstName |LastName |FamilyDetails                                                                                                             |
+----------+---------+--------------------------------------------------------------------------------------------------------------------------+
|Emma      |Smith    |{                                                                                                                         |
|          |         | "23214598.31601190":{"gender":"F","Name":"Ms Olivia Smith","relationship":"Daughter"},                                   |
|          |         | "23214598.23214598":{"gender":"F","Name":"Ms Emma Smith","relationship":null}                                            |
|          |         |}                                                                                                                         |
|Joe       |Williams |{                                                                                                                         |
|          |         |  "2321463.2321463":{"gender":"M","Name":"Mr Joe Williams","relationship":null},                                          |
|          |         |  "2321463.3841483":{"gender":"F","Name":"Mrs Sophia Williams","relationship":"Wife","IsActive":"N"}                      |
|          |         |}                                                                                                                         |
|Liam      |Jones    |{                                                                                                                         |
|          |         |  "2321464.12379942":{"gender":"F","Name":"Miss Patricia Jones","relationship":"Sister"},                                 |
|          |         |  "2321464.2321464":{"gender":"M","Name":"Mr Liam Jones","relationship":null,"IsActive":"Y"}                              |
|          |         |}                                                                                                                         |
+----------+---------+--------------------------------------------------------------------------------------------------------------------------+

What I am trying to do:

I am trying to get records where we have inactive family members (IsActive='N'). Point to note that IsActive is an optional field.

Expected output:

+----------+---------+--------------------------------------------------------------------------------------------------------------------------+
|FirstName |LastName |FamilyDetails                                                                                                             |
+----------+---------+--------------------------------------------------------------------------------------------------------------------------+                                                                                                                   |
|Joe       |Williams |{                                                                                                                         |
|          |         |  "2321463.2321463":{"gender":"M","Name":"Mr Joe Williams","relationship":null},                                          |
|          |         |  "2321463.3841483":{"gender":"F","Name":"Mrs Sophia Williams","relationship":"Wife","IsActive":"N"}                      |
|          |         |}                                                                                                                         |
+----------+---------+--------------------------------------------------------------------------------------------------------------------------+

What I have tried so far:

Since the complete schema is unknown, I tried to create the schema from FamilyDetails column itself.

import org.apache.spark.sql.functions._
import spark.implicits._
val json_schema = spark.read.json(myDF.select("FamilyDetails").as[String]).schema
println(json_schema)

which gives me:

StructType(
    StructField(23214598.31601190,
        StructType(
            StructField(gender,StringType,true), 
            StructField(Name,StringType,true), 
            StructField(relationship,StringType,true), 
            StructField(IsActive,StringType,true)
        )
        ,true
    )
)

How do I get rid of the first value (2321463.2321463) and take only the required fields in the json schema? Or is there any easier approach to filter records where IsActive = 'N'?

mck · Accepted Answer · 2021-03-24 10:44:49Z

1

Perhaps you can avoid parsing the JSON by simply finding the string "IsActive":"N":

val df2 = df.filter("""FamilyDetails rlike '"IsActive":"N"'""")

For more rigorous parsing, you can use:

val df2 = df.filter("exists(map_values(from_json(FamilyDetails, 'map<string,map<string,string>>')), x -> x['IsActive'] = 'N')")

edited Mar 24, 2021 at 10:44

answered Mar 24, 2021 at 9:38

mck

42.7k13 gold badges44 silver badges62 bronze badges

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

3 Comments

user2538559 Over a year ago

Yes, this would have been a simpler solution! But, I need to run a few more checks on the data. for example: records where gender contains a value other than ["M","F"], records where name length is more than 20 characters, records where age is more than 3 digits, etc.

user2538559 Over a year ago

Is there any way to access a specific key-value pair from the string, like "IsActive"="N" or "gender"="M"? Using regex or something?

mck Over a year ago

@user2538559 see edited answer. You can use some Spark SQL functions.

Collectives™ on Stack Overflow

Filter based on JSON data which is in a string column in a Spark dataframe

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related