Parsing nodes on JSON with Scala -

Question

I've been asked to parse a JSON file to get all the buses that are over a specified speed inputed by the user.

The JSON file can be downloaded here

It's like this:

{
"COLUMNS": [
    "DATAHORA",
    "ORDEM",
    "LINHA",
    "LATITUDE",
    "LONGITUDE",
    "VELOCIDADE"
],
"DATA": [
    [
        "04-16-2015 00:00:55",
        "B63099",
        "",
        -22.7931,
        -43.2943,
        0
    ],
    [
        "04-16-2015 00:01:02",
        "C44503",
        781,
        -22.853649,
        -43.37616,
        25
    ],
    [
        "04-16-2015 00:11:40",
        "B63067",
        "",
        -22.7925,
        -43.2945,
        0
    ],
]
}

The thing is: I'm really new to scala and I have never worked with json before (shame on me). What I need is to get the "Ordem", "Linha" and "Velocidade" from DATA node.

I created a case class to enclousure all the data so as to later look for those who are over the specified speed.

case class Bus(ordem: String, linha: Int, velocidade: Int)

I did this reading the file as a textFile and spliting. Although this way, I need to foreknow the content of the file in order to go to the lines after DATA node.

I want to know how to do this using a JSON parser. I've tried many solutions, but I couldn't adapt to my problem, because I need to extract all the lines from DATA node instead of nodes inside one node.

Can anyone help me?

PS: Sorry for my english, not a native speaker.

Answer 1

First of all, you need to understand the different JSON data types. The basic types in JSON are numbers, strings, booleans, arrays, and objects. The data returned in your example is an object with two keys: COLUMNS and DATA. The COLUMNS key has a value that is an array of strings and numbers. The DATA key has a value which is an array of arrays of strings.

You can use a library like PlayJSON to work with this type of data:

val js = Json.parse(x).as[JsObject]
val keys = (js \ "COLUMNS").as[List[String]]
val values = (js \ "DATA").as[List[List[JsValue]]]

val busses = values.map(valueList => {
    val keyValues = (keys zip valueList).toMap 
    for {
      ordem <- keyValues("ORDEM").asOpt[String]
      linha <- keyValues("LINHA").asOpt[Int]
      velocidade <- keyValues("VELOCIDADE").asOpt[Int]
    } yield Bus(ordem, linha, velocidade)
})

Note the use of asOpt when converting the properties to the expected types. This operator converts the key-values to the provided type if possible (wrapped in Some), and returns None otherwise. So, if you want to provide a default value instead of ignoring other results, you could use keyValues("LINHA").asOpt[Int].getOrElse(0), for example.

You can read more about the Play JSON methods used here, like \ and as, and asOpt in their docs.

Answer 2

You can use Spark SQL to achieve it. Refer section under JSON Datasets here

In essence, Use spark APIs to load a JSON and register it as temp table. You can run your SQL queries on the table from there.

Answer 3

As seen on @Ben Reich answer, that code works great. Thank you very much.

Although, my Json had some type problems on "Linha". As it can be seen on the JSON example that I put on the Question, there are "" and also numbers, e.g., 781.

When trying to do keyValues("LINHA").asOpt[Int].getOrElse(0), it was producing an error saying that value flatMap is not a member of Int. So, I had to change some things:

case class BusContainer(ordem: String, linha: String, velocidade: Int)
val jsonString = fromFile("./project/rj_onibus_gps.json").getLines.mkString
val js = Json.parse(jsonString).as[JsObject]
val keys = (js \ "COLUMNS").as[List[String]]
val values = (js \ "DATA").as[List[List[JsValue]]]

val buses = values.map(valueList => {
  val keyValues = (keys zip valueList).toMap
  println(keyValues("ORDEM"),keyValues("LINHA"),keyValues("VELOCIDADE"))
  for {
    ordem <- keyValues("ORDEM").asOpt[String]
    linha <- keyValues("LINHA").asOpt[Int].orElse(keyValues("LINHA").asOpt[String])
    velocidade <- keyValues("VELOCIDADE").asOpt[Int]
  } yield BusContainer(ordem, linha.toString, velocidade)
})

Thanks for the help!