Where are custom field options in the data lake/analytics?

[Flemming Grove]

In Jira, we have a custom field with Planning Interval (PI) options. Now I would like to create a custom table mapping sprints to the PI values (options) defined for the custom field. But I cannot find a table containing the defined options. Where is it? 

[Mohsin Shaikh · Accepted solution]

Hello Flemming,

I hope you are doing well.

Short answer: The Atlassian Data Lake does not currently expose a standalone table of custom field option definitions. There is no table that simply lists all the configured options (e.g., all your PI values) for a given custom field independently of issues.

Where custom field values do live

The values actually set on issues are stored in the Work item field table (jira_issue_field). For your Planning Interval field, you would find rows where:

• Name = your field name (e.g., Planning Interval)

• Value = the option value currently assigned to each issue

You can query this in SQL mode:

SELECT DISTINCT value FROM jira_issue_field WHERE name = 'Planning Interval'

This gives you the PI values that are currently in use across your issues. However, it will not return options that are defined on the field but not assigned to any issue today.

https://confluence.atlassian.com/spaces/ANALYTICSKB/pages/1188411074/Query+Jira+custom+fields 

Why there is no "options definition" table

The current Data Lake schema is issue-centric: field values are only exposed in the context of issues they are assigned to. Site-level configuration data (field option lists, status definitions independent of issues, etc.) is not yet surfaced as its own table. This is a known gap/ limitation.

Workaround: build a custom table for your sprint-to-PI mapping

Since there is no built-in options table, the most practical path is to create a custom table in your Data Lake connection schema that maps sprints to PI values. You have two approaches:

1. Derive from existing issue data - Query distinct PI values from jira_issue_field and join with sprint data from the Sprint table. This only captures options currently in use.

2. Manually define the mapping - If your PI options are relatively static, you can hard-code the sprint-to-PI associations directly in a custom table using a VALUES clause or UNION ALL pattern.

Documentation on creating custom tables:
Query Jira custom fields with custom columns or custom tables | Atlassian Analytics | Atlassian Support

I hope this helps.

Regards,

Mohsin

[Flemming Grove]

Hi Mohsin

Thank you for clarifying about the Data Lake schema being issue-centric.

When you say "current", are there any plans to add more schemas or extend the current one? Fx it surprised me that only the last layer of transformed data is exposed in the Atlassian Analytics (I still need to look into what can be made available to other tools via data shares).

Thank you also for the proposed workaround. I have serious doubts if that will work with sufficient performance on out data. But it is worth a try.

Have a good day,
Flemming