Seeking Assistance with Jira API: Extracting Custom Field Values Based on Conditions

[vdtmik88]

Dear Jira Community,

I hope this message finds you well. I am currently facing a challenge in extracting data from Jira using its REST API, specifically related to custom fields.

The situation involves two custom fields, which I'll refer to as "year" and "team." The complication arises as the "team" field is only visible when a specific value is set in the "year" field. To elaborate, I can retrieve all available values for "year" and "team" separately using direct requests. For instance, I can obtain all teams associated with a particular year with a command like:

curl -H "Content-Type: application/json" -H "Host: jira.b.ee" https://jira.b.de/rest/api/2/issue/createmeta/Event/issuetypes/11300 | jq '.values[16].allowedValues[].value'

Or retrieve all available years with:

curl -H "Content-Type: application/json" -H "Host: jira.b.ee" https://jira.b.de/rest/api/2/issue/createmeta/Event/issuetypes/11300 | jq '.values[15].allowedValues[].value'

However, my goal is to process this data and store it in another MySQL database, specifically filtering and extracting teams that played in a certain year, for example, teams that played only in 1999.

I would greatly appreciate any guidance, suggestions, or sample scripts that could help me achieve this data extraction and processing task. If anyone has encountered a similar scenario or has expertise in utilizing Jira's REST API for such requirements, your insights would be invaluable.

Thank you in advance for your time and assistance. Your contributions are highly appreciated, and I look forward to learning from the community's expertise.

Best regards,

Vitalii

[Pablo Brincat]

Hello Vitalii,

Your project with the JIRA REST API sounds interesting, and I’m happy to offer some guidance on how you can achieve your goal of extracting and processing data from custom fields. Since you’re dealing with conditional visibility of fields, this adds a layer of complexity, but it’s definitely manageable.

Here’s a general approach you can take:

Understanding JIRA’s REST API

1. JIRA API Endpoints: You’re already using the API to retrieve values for “year” and “team.” The key here is to understand how these fields are linked in the JIRA schema. Normally, custom fields in JIRA don’t have direct relationships unless they’re part of a cascading select list or similar.

2. Conditional Field Visibility: The fact that the “team” field is only visible for specific “year” values implies there’s a workflow or a screen scheme making this happen. It’s important to understand this logic to know when to query for “team” values.

Scripting the Data Extraction

1. Extracting Year Values: Continue using your current method to retrieve all available “year” values.

2. Iterating Over Years: For each year, check if the “team” field is available. You might need to use a JIRA issue that you know will have the “team” field visible for that year as a reference.

3. Extracting Team Values: If the “team” field is visible for a year, use the API to fetch the “team” values for that year.

4. Data Processing: Once you have the year and team data, you can filter this on the server-side (in your script) to find teams associated with a specific year like 1999.

Storing Data in MySQL

1. Database Schema: In your MySQL database, you’d likely want a table structure that can store the relationship between years and teams.

2. Inserting Data: Use your script to insert the relevant data into MySQL, ensuring that you’re only storing teams that meet your specified condition (like playing in 1999).

Sample Script Outline

import requests

# Your JIRA API details
jira_base_url = "https://jira.b.de"
api_endpoint = "/rest/api/2/issue/createmeta/Event/issuetypes/11300"
headers = {"Content-Type": "application/json", "Host": "jira.b.ee"}

# Fetching years
years_response = requests.get(f"{jira_base_url}{api_endpoint}", headers=headers)
years = years_response.json()  # process JSON to get years

for year in years:
    # Check if 'team' field is available for this 'year'
    # You might need to adjust this part based on how JIRA returns the data
    if team_field_is_visible(year):
        # Fetch team data
        team_response = requests.get(f"{jira_base_url}{api_endpoint}", headers=headers)
        teams = team_response.json()  # process JSON to get teams
        # Process and store data in MySQL
        # ...

def team_field_is_visible(year):
    # Implement logic to check if 'team' field is visible for the given 'year'
    # This could involve querying a specific issue where you know the field should be visible
    return True  # or False based on the condition

# MySQL storage logic here
# ...

Additional Tips

• Error Handling: Implement robust error handling, especially for network requests and database operations.
• API Rate Limiting: Be aware of JIRA’s API rate limits to avoid being blocked.
• Script Optimization: Depending on the size of the data, you may want to optimize your script for performance.

This approach requires a good understanding of how your JIRA instance is set up and how the custom fields are managed. If the logic of field visibility is complex, you might need to adjust the script accordingly.

Good luck with your project, and feel free to reach out if you need further assistance! 🚀👨‍💻