[Python] Freshdesk API Trying to get Username for a hierarchy field

I've been trying to work through the Freshdesk api's and pull in username with some other fields. The issue I'm having is username is in a child field but when I try to grab it I get a bunch of null fields for the username. Is there something I'm missing here?

import requests
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType, IntegerType

# Initialize Spark session
spark = SparkSession.builder.master("local[1]").appName('SparkApp').getOrCreate()

# Define the schema
schema = StructType([
StructField("System_Id", StringType(), True),
StructField("System_WorkItemType", StringType(), True),
StructField("System_TeamProject", StringType(), True),
StructField("System_Title", StringType(), True),
StructField("TrackedTotal", IntegerType(), True),
StructField("TrackedItself", IntegerType(), True),
StructField("HasChildren", StringType(), True),
StructField("User_Name", StringType(), True)
])

# Define the URL and parameters
table_name = "workItemsHierarchy"
url = f"https://MYCOMPANY.timehub.7pace.com/api/odata/v3.2/{table_name}"
params = {
"$select": "System_Id,System_WorkItemType,System_TeamProject,System_Title,TrackedTotal,TrackedItself,HasChildren",
"$filter": "HasChildren eq true",
"$top": 100, # Number of items per page
"$skip": 0 # Starting point for pagination
}
headers = {
"Authorization": f"Bearer {api_key}",
"Content-Type": "application/json"
}
body = {
"hierarchyField": "System_Id",
"childrenQuery": "({hierarchyField})/AllWorklogs?$apply=groupby((User/Name),aggregate(PeriodLength with sum as TrackedTotal))",
"hierarchyType": "rootExpandable",
"columns": [
{
"field": "System_Id",
"title": "ID",
"formatter": "WorkItemId",
"width": 60
},
{
"field": ["System_Title", "System_WorkItemType", "System_TeamProject"],
"title": "Title",
"formatter": "WorkItemIndicator",
"width": -100,
"expand": True,
"child": {
"field": "User.Name"
}
},
{
"field": "TrackedTotal",
"title": "Tracked",
"width": 100,
"formatter": "TimeLength"
}
],
"autoSubtitle": True,
"timeframeFilter": None,
"childrenEndpoint": "workItemsHierarchy",
"y": 4,
"x": 0
}

all_data = []
while True:
# Make the GET request
response = requests.get(url, params=params, headers=headers, json=body)

# Check if the request was successful
if response.status_code == 200:
data = response.json()
all_data.extend(data['value']) # Assuming the data is in 'value' key

# Check if there's more data to fetch
if '@odata.nextLink' in data:
url = data['@odata.nextLink']
else:
break
else:
print(f"Request failed with status code {response.status_code}.")
break

# Convert the data to a format suitable for PySpark
formatted_data = [
{
"System_Id": item.get("System_Id"),
"System_WorkItemType": item.get("System_WorkItemType"),
"System_TeamProject": item.get("System_TeamProject"),
"System_Title": item.get("System_Title"),
"TrackedTotal": item.get("TrackedTotal"),
"TrackedItself": item.get("TrackedItself"),
"HasChildren": item.get("HasChildren"),
"User_Name": item.get("User", {}).get("Name") if item.get("User") else None
}
for item in all_data
]

# Create a PySpark DataFrame with the defined schema
df = spark.createDataFrame(formatted_data, schema)

# Show the DataFrame
df.show()



I've tried putting "User.Name" and "User/Name" in the select statement but both of those errored out.

Continue reading...