How to parse JsonArray in Scala and writing them in a DataFrame?

Viewed 178

Using my Scala HTTP Client I retrieved a response in JSON format from an API GET call.

My end goal is to write this JSON content to an AWS S3 bucket in order to make it available as a table on RedShift running a simple AWS Glue crawler.

My thinking is to parse this JSON message and somehow converting into a Spark DataFrame, so later on I can save it to my preferred S3 location in the format of .csv, .parquet, or whatever.

The JSON file looks like this

{
  "response": {
    "status": "OK",
    "start_element": 0,
    "num_elements": 100,
    "categories": [
      {
        "id": 1,
        "name": "Airlines",
        "is_sensitive": false,
        "last_modified": "2010-03-19 17:48:36",
        "requires_whitelist_on_external": false,
        "requires_whitelist_on_managed": false,
        "is_brand_eligible": true,
        "requires_whitelist": false,
        "whitelist": {
          "geos": [],
          "countries_and_brands": []
        }
      },
      {
        "id": 2,
        "name": "Apparel",
        "is_sensitive": false,
        "last_modified": "2010-03-19 17:48:36",
        "requires_whitelist_on_external": false,
        "requires_whitelist_on_managed": false,
        "is_brand_eligible": true,
        "requires_whitelist": false,
        "whitelist": {
          "geos": [],
          "countries_and_brands": []
        }
      }
    ],
    "count": 148,
    "dbg_info": {
      "warnings": [],
      "version": "1.18.1621",
      "output_term": "categories"
    }
  }
}

The content I would like to map to a Dataframe is the one contained by the "categories" JSON Array.

I have managed to parse the message using json4s.JsonMethods method parse this way:

val parsedJson = parse(request) \\ "categories"

Obtaining the following:

output: org.json4s.JValue = JArray(List(JObject(List((id,JInt(1)), (name,JString(Airlines)), (is_sensitive,JBool(false)), (last_modified,JString(2010-03-19 17:48:36)), (requires_whitelist_on_external,JBool(false)), (requires_whitelist_on_managed,JBool(false)), (is_brand_eligible,JBool(true)), (requires_whitelist,JBool(false)), (whitelist,JObject(List((geos,JArray(List())), (countries_and_brands,JArray(List()))))))), JObject(List((id,JInt(2)), (name,JString(Apparel)), (is_sensitive,JBool(false)), (last_modified,JString(2010-03-19 17:48:36)), (requires_whitelist_on_external,JBool(false)), (requires_whitelist_on_managed,JBool(false)), (is_brand_eligible,JBool(true)), (requires_whitelist,JBool(false)), (whitelist,JObject(List((geos,JArray(List())), (countries_and_brands,JArray(List()))))))))

However, I am completely lost on how to proceed. I have even tried using another library for Scala called uJson:

val json = (ujson.read(request))
val tuples = json("response")("categories").arr /* <-- categories is an array */ .map { item => 
(item("id"), item("name")) 

This time I have only parsed two fields for testing, but this shouldn't change much. Hence, I obtained the following structure:

tuples: scala.collection.mutable.ArrayBuffer[(ujson.Value, ujson.Value, ujson.Value, ujson.Value)] = ArrayBuffer((1,"Airlines",false,"2010-03-19 17:48:36"), (2,"Apparel",false,"2010-03-19 17:48:36"))

However, also this time I do not know how to move forward and everything I try results in errors, mostly related to format incompatibility.

Please, feel free to propose any other approach to achieve my goal even if it changes totally my workflow. I rather learn something properly. Thanks

1 Answers

We can use the following code to convert JSON to Spark Dataframe/Dataset

val df00 = spark.read.option("multiline","true").json(Seq(JSON_OUTPUT).toDS())

Related