Retrieving data from a JSON object stored within a database column

Question

Retrieving data from a JSON object stored within a database column

There is a dataframe presented below:

+-------+--------------------------------
|__key__|______value____________________| 
|  1    | {"name":"John", "age": 34}    |
|  2    | {"name":"Rose", "age": 50}    |

The goal is to extract all the age values from this dataframe and store them in an array.

val x = df_clean.withColumn("value", col("value.age"))
x.show(false)

An error occurs when trying to execute this operation.

Error message: Exception in thread "main" org.apache.spark.sql.AnalysisException: Can't extract value from value#89: need struct type but got string;

How can this issue be resolved?

EDIT

 val schema = existingSparkSession.read.json(df_clean.select("value").as[String]).schema
    val my_json = df_clean.select(from_json(col("value"), schema).alias("jsonValue"))
    my_json.printSchema()
    val df_final = my_json.withColumn("age", col("jsonValue.age"))
    df_final.show(false)

No errors are thrown after implementing these changes. However, there is no visible output as well.

EDIT 2

println("---+++++--------")
df_clean.select("value").take(1)
println("---+++++--------")

Output:

---+++++--------
---+++++--------

json scala dataframe apache-spark apache-spark-sql

Answer 1

Answer №1

To generate a schema for long JSON data, you can utilize the from_json function along with a schema.

import org.apache.spark.sql.functions._

val df = Seq(
  (1, "{\"name\":\"John\", \"age\": 34}"),
  (2, "{\"name\":\"Rose\", \"age\": 50}")
).toDF("key", "value")

val schema = spark.read.json(df.select("value").as[String]).schema

val resultDF = df.withColumn("value", from_json($"value", schema))

resultDF.show(false)
resultDF.printSchema()

Expected Output:

+---+----------+
|key|value     |
+---+----------+
|1  |{34, John}|
|2  |{50, Rose}|
+---+----------+

Generated Schema:

root
 |-- key: integer (nullable = false)
 |-- value: struct (nullable = true)
 |    |-- age: long (nullable = true)
 |    |-- name: string (nullable = true)

If you need to access nested fields directly, you can use the get_json_object function.

df.withColumn("name", get_json_object($"value", "$.name"))
  .withColumn("age", get_json_object($"value", "$.age"))
  .show(false)

Answer 2

To generate a schema for long JSON data, you can utilize the from_json function along with a schema.

import org.apache.spark.sql.functions._

val df = Seq(
  (1, "{\"name\":\"John\", \"age\": 34}"),
  (2, "{\"name\":\"Rose\", \"age\": 50}")
).toDF("key", "value")

val schema = spark.read.json(df.select("value").as[String]).schema

val resultDF = df.withColumn("value", from_json($"value", schema))

resultDF.show(false)
resultDF.printSchema()

Expected Output:

+---+----------+
|key|value     |
+---+----------+
|1  |{34, John}|
|2  |{50, Rose}|
+---+----------+

Generated Schema:

root
 |-- key: integer (nullable = false)
 |-- value: struct (nullable = true)
 |    |-- age: long (nullable = true)
 |    |-- name: string (nullable = true)

If you need to access nested fields directly, you can use the get_json_object function.

df.withColumn("name", get_json_object($"value", "$.name"))
  .withColumn("age", get_json_object($"value", "$.age"))
  .show(false)

Retrieving data from a JSON object stored within a database column

Answer №1

Similar questions

The system is unable to locate a supporting entity with the identifier '[object Object]', as it is classified as an 'object'

Using Node.js, I am utilizing a Javascript API to perform a POST request, reading data

Struggling to convert JSON data into a variable, but all I'm getting is a null response

Creating an efficient post request using retrofit

What is the best way to transform a JSON object from a remote source into an Array using JavaScript?

I'm puzzled as to why my HTTP request in Node.js is returning an empty body

How can a custom format structure be established for the json export feature in Scrapy? If it is possible, what is the process for doing so

JSON representing an array of strings in JavaScript

An issue arises when using JSON.parse() with regular expression values

Combining JSON elements using JsonPath (JayWay)

Saving data in a JSON format within the browser's localStorage, whether it be a nested object-tree or a collection

Retrieve the JSON data once the user has applied the filter

Tips for implementing an Item loader in my Scrapy spider script?

Customizing push notifications for multiple servers on iOS with unique messages

Iterating Through an Array using Foreach Loop in PHP after using json_decode

Could one potentially use jQuery to navigate through JSON output?

Searching for a specific value in various Json files: A guide

What is the best way to send an HTTP request in AngularJS to receive data in JSON format?

How to parse JSON in JavaScript/jQuery while preserving the original order

Efficiently loading data using lazy loading in jsTree