udf to transform a json string into multiple rows based on first level of nesting

Question

I am trying to transform a df based on the first level nesting in the json string.

input dataframe

+------+------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|prefix|cid                                 |data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------+------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|gsl   |ff05be88-cf81-4140-8393-0f29b9cca89d|{"ec":{"EC_OTF_REWARD_10_EC_OTF_REWARD_101696012269":{"t":{"FDFS":[{"id":"FDFS_1","cTs":1696370565176,"st":"CREATED","meta":{}}],"FSNM":[{"id":"FSNM_2","cTs":1696370565176,"st":"CREATED","meta":{}}]},"cTs":1696370565176,"d":30}}}                                                                                                                                                                                                                                                                                                                         |
|gsl   |297f7df3-7416-4987-80c3-c03a43e3bef8|{"gs":{"IOB_REWARD10":{"t":{"APP_LOGIN":[{"id":"APP_LOGIN_1","cTs":1714175394086,"st":"CREATED","meta":{}}],"FSNM":[{"id":"FSNM_3","cTs":1714175394086,"st":"CREATED","meta":{}}],"PPLUS":[{"id":"PARAMOUNT_1","cTs":1714175394086,"st":"CREATED","meta":{}}]},"cTs":1714175394086,"d":30}}}                                                                                                                                                                                                                                                                  |
|gsl   |00005eb6-dca0-4d1a-862f-946fe827861a|{"gs":{"GAMIFIED_SEGMENT10":{"t":{"VIDEO":[{"id":"VIDEO_1","cTs":1685751643256,"st":"COMPLETE","meta":{}}],"FDFS":[{"id":"FDFS_3","cTs":1685751643256,"st":"COMPLETE","meta":{}}],"REWARD":[{"id":"ITEM_REWARD_1","cTs":1685751643256,"st":"COMPLETE","meta":{"th":1}}]},"cTs":1685751643256,"d":30,"uTs":1686420807959}},"ec":{"EC_OTF_REWARD_10_EC_OTF_REWARD_101697055998":{"t":{"FDFS":[{"id":"FDFS_1","cTs":1697173860300,"st":"CREATED","meta":{}}],"FSNM":[{"id":"FSNM_2","cTs":1697173860300,"st":"CREATED","meta":{}}]},"cTs":1697173860300,"d":30}}}|
+------+------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

e.g:: the json string {"gs":{"GAMIFIED_SEGMENT10":{"t":{"VIDEO":[{"id":"VIDEO_1","cTs":1685751643256,"st":"COMPLETE","meta":{}}],"FDFS":[{"id":"FDFS_3","cTs":1685751643256,"st":"COMPLETE","meta":{}}],"REWARD":[{"id":"ITEM_REWARD_1","cTs":1685751643256,"st":"COMPLETE","meta":{"th":1}}]},"cTs":1685751643256,"d":30,"uTs":1686420807959}},"ec":{"EC_OTF_REWARD_10_EC_OTF_REWARD_101697055998":{"t":{"FDFS":[{"id":"FDFS_1","cTs":1697173860300,"st":"CREATED","meta":{}}],"FSNM":[{"id":"FSNM_2","cTs":1697173860300,"st":"CREATED","meta":{}}]},"cTs":1697173860300,"d":30}}}

will be created as 2 rows

{"gs":{"GAMIFIED_SEGMENT10":{"t":{"VIDEO":[{"id":"VIDEO_1","cTs":1685751643256,"st":"COMPLETE","meta":{}}],"FDFS":[{"id":"FDFS_3","cTs":1685751643256,"st":"COMPLETE","meta":{}}],"REWARD":[{"id":"ITEM_REWARD_1","cTs":1685751643256,"st":"COMPLETE","meta":{"th":1}}]},"cTs":1685751643256,"d":30,"uTs":1686420807959}}}

{"ec":{"EC_OTF_REWARD_10_EC_OTF_REWARD_101697055998":{"t":{"FDFS":[{"id":"FDFS_1","cTs":1697173860300,"st":"CREATED","meta":{}}],"FSNM":[{"id":"FSNM_2","cTs":1697173860300,"st":"CREATED","meta":{}}]},"cTs":1697173860300,"d":30}}}    

   




                                                                

expected output df:

    +------+------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    |prefix|cid                                 |data                                                                                                                                                                                                                                                                                                                      |
    +------+------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    |gsl   |ff05be88-cf81-4140-8393-0f29b9cca89d|{"ec":{"EC_OTF_REWARD_10_EC_OTF_REWARD_101696012269":{"t":{"FDFS":[{"id":"FDFS_1","cTs":1696370565176,"st":"CREATED","meta":{}}],"FSNM":[{"id":"FSNM_2","cTs":1696370565176,"st":"CREATED","meta":{}}]},"cTs":1696370565176,"d":30}}}                                                                                     |
    |gsl   |297f7df3-7416-4987-80c3-c03a43e3bef8|{"gs":{"IOB_REWARD10":{"t":{"APP_LOGIN":[{"id":"APP_LOGIN_1","cTs":1714175394086,"st":"CREATED","meta":{}}],"FSNM":[{"id":"FSNM_3","cTs":1714175394086,"st":"CREATED","meta":{}}],"PPLUS":[{"id":"PARAMOUNT_1","cTs":1714175394086,"st":"CREATED","meta":{}}]},"cTs":1714175394086,"d":30}}}                              |
    |gsl   |00005eb6-dca0-4d1a-862f-946fe827861a|{"gs":{"GAMIFIED_SEGMENT10":{"t":{"VIDEO":[{"id":"VIDEO_1","cTs":1685751643256,"st":"COMPLETE","meta":{}}],"FDFS":[{"id":"FDFS_3","cTs":1685751643256,"st":"COMPLETE","meta":{}}],"REWARD":[{"id":"ITEM_REWARD_1","cTs":1685751643256,"st":"COMPLETE","meta":{"th":1}}]},"cTs":1685751643256,"d":30,"uTs":1686420807959}}}|
    |gsl   |00005eb6-dca0-4d1a-862f-946fe827861a|{"ec":{"EC_OTF_REWARD_10_EC_OTF_REWARD_101697055998":{"t":{"FDFS":[{"id":"FDFS_1","cTs":1697173860300,"st":"CREATED","meta":{}}],"FSNM":[{"id":"FSNM_2","cTs":1697173860300,"st":"CREATED","meta":{}}]},"cTs":1697173860300,"d":30}}}                                                                                     |
    +------+------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I have written this piece of code but it's not giving desired results

private val extractBlobsUDF = udf((json: String) => {
val parsed = JSON.parseFull(json)
parsed match {
  case Some(map: Map[String, Any]) =>
    map.flatMap {
      case ("gs", gsMap) =>
        gsMap.asInstanceOf[Map[String, Any]].map { case (key, value) =>
          ("gsl", key, value.toString)
        }
      case ("ec", ecMap) =>
        ecMap.asInstanceOf[Map[String, Any]].map { case (key, value) =>
          ("ec", key, value.toString)
        }
      // Add additional cases for other possible blobs if needed
      case _ => Seq.empty[(String, String, String)]
    }.toList
  case _ => Seq.empty[(String, String, String)]
}

})

---

**The issue is resolved. hence closing this thread