topic Re: sample in Data Engineering

sample

NathanSundarara — Sat, 13 May 2023 01:36:41 GMT

Help parsing the JSON using Spark SQL or python. Sample json attached.

Re: sample

Anonymous — Sat, 13 May 2023 17:11:58 GMT

@Nathan Sundararajan :

Sure! Here is an example of how to parse the JSON data using Python:

import json
 
# Load the JSON data from a file or string
data = {...}  # Your JSON data here
 
# Parse the JSON data into a Python object
obj = json.loads(data)
 
# Access the data using Python syntax
message_timestamp = obj["messageTimestamp"]
site_reference = obj["siteReference"]
update_count = obj["updateCount"]
updates = obj["updates"]
 
# Iterate through the updates array and access nested data
for update in updates:
    event_timestamp = update["eventTimestamp"]
    spot_reference = update["spotReference"]
    spot_info = update["spotInfo"]
    vehicle_spot = spot_info["vehicleSpot"]
    vehicle_data = spot_info["vehicleData"]
    # Access more nested data as needed...

Alternatively, you can also use Spark SQL to parse the JSON data. Here is an example using the

spark.read.json() method:

from pyspark.sql import SparkSession
 
# Create a SparkSession object
spark = SparkSession.builder.appName("JSON Parsing").getOrCreate()
 
# Load the JSON data from a file or string
data = {...}  # Your JSON data here
 
# Create a DataFrame from the JSON data
df = spark.read.json(sc.parallelize([data]))
 
# Register the DataFrame as a temporary view
df.createOrReplaceTempView("json_data")
 
# Query the data using Spark SQL syntax
result = spark.sql("""
    SELECT messageTimestamp, siteReference, updateCount, updates.eventTimestamp, updates.spotReference,
           updates.spotInfo.vehicleSpot.externalReference, updates.spotInfo.vehicleSpot.state,
           updates.spotInfo.vehicleData.externalReference, updates.spotInfo.vehicleData.state
    FROM json_data
    LATERAL VIEW EXPLODE(updates) AS updates
""")
 
# Show the result
result.show()

This query selects some of the fields from the JSON data and explodes the updates array into separate rows. You can modify the query as needed to access more or less data.

Re: sample

NathanSundarara — Sat, 13 May 2023 20:51:54 GMT

Can you explain how to go all the way to one of the array.. c3fieldstrings inside the field structure please?

"fields": {

"c3FieldStrings": [

{

"externalReference": "4095",

"name": "Blocking Spot"

{

"externalReference": "Disabled",

"name": "Disabled",

"value": "false"

{

"externalReference": "WorkflowVehicleSpot",

"name": "Spot",

"value": "AA18_FL5"

{

"externalReference": "WorkflowVMT",

"name": "WorkflowVMT"

Re: sample

NathanSundarara — Mon, 22 May 2023 12:45:16 GMT

@Suteja Kanuri can you please respond to my question above?

Re: sample

Anonymous — Fri, 09 Jun 2023 09:40:22 GMT

@Nathan Sundararajan : Does the below help?

data = {
    "fields": {
        "c3FieldStrings": [
            {
                "externalReference": "4095",
                "name": "Blocking Spot"
            },
            {
                "externalReference": "Disabled",
                "name": "Disabled",
                "value": "false"
            },
            {
                "externalReference": "WorkflowVehicleSpot",
                "name": "Spot",
                "value": "AA18_FL5"
            },
            {
                "externalReference": "WorkflowVMT",
                "name": "WorkflowVMT"
            }
        ]
    }
}
 
c3FieldStrings = data["fields"]["c3FieldStrings"]