In Spark SQL, transform JSON key name into value

Question

It seems like there should be a function for this in Spark SQL similar to pivoting, but I haven't found any solution to transforming a JSON key into a a value. Suppose I have a badly formed JSON (the format of which I cannot change):

{"A long string containing serverA": {"x": 1, "y": 2}}

how can I process it to

{"server": "A", "x": 1, "y": 2}

?

I read the JSONs into an an sql.dataframe and would then like to process them as described above:

val cs = spark.read.json("sample.json")
  .???

A direct transformation of the key name into a key-value pair, as shown in the example, would be the neatest way, but I would also accept something like "newkey": "A long string containing serverA" — Matthias Gralle
– Matthias Gralle, Commented Nov 8, 2021 at 14:01

Oli · Accepted Answer · 2021-11-08 14:40:36Z

1

If we want to use only spark functions and no UDFs, you could use from_json to parse the json into a map (we need to specify a schema). Then you just need to extract the information with spark functions. One way to do it is as follows:

val schema = MapType(
    StringType,
    StructType(Array(
        StructField("x", IntegerType),
        StructField("y", IntegerType)
    ))
)

spark.read.text("...")
    .withColumn("json", from_json('value, schema))
    .withColumn("key", map_keys('json).getItem(0))
    .withColumn("value", map_values('json).getItem(0))
    .withColumn("server",
        // Extracting the server name with a regex
        regexp_replace(regexp_extract('key, "server[^ ]*", 0), "server", ""))
    .select("server", "value.*")
    .show(false)

which yields:

+------+---+---+
|server|x  |y  |
+------+---+---+
|A     |1  |2  |
+------+---+---+

answered Nov 8, 2021 at 14:40

Oli

10.5k5 gold badges31 silver badges51 bronze badges

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

9 Comments

Matthias Gralle Over a year ago

Thanks, Oli! This would work perfectly with my toy dataset. In my real dataset I have many more key-value mappings in the inner JSON, and I wonder if I can do the transformation without having to specify a schema.

Oli Over a year ago

With from_json you need to specify the schema but there are other ways to go at it. What's your json like exactly?

Matthias Gralle Over a year ago

It is from a client and I am not sure if I can post the entire structure. It goes like

{   "Statistics for client XXX": {     "ipaddress": "XX.XXX.XXX.XXX",     "totalAccessRequests": 243,     "totalDupAccessRequests": 0,     "totalAccessAccepts": 51,

and so on, with 17 numeric values. I can of course write a schema like you did here, but at that point I wonder if I go a completely different route (parsing into a case class in scala)

Matthias Gralle Over a year ago

In any case I will accept your answer since it works perfectly for the toy dataset I had originally posted.

Oli Over a year ago

Thanks, but that does not solve your problem :)

|

Collectives™ on Stack Overflow

In Spark SQL, transform JSON key name into value

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related