Querying for Advanced Roadmaps for Jira Custom Fields
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Querying the custom fields for the Advanced Roadmaps for Jira plugin might be a challenge. That happens because their values aren't stored in the customfieldvalue table, as described in https://developer.atlassian.com/server/jira/platform/database-custom-fields/.
Instead, they are stored as JSON values in the entity_property table.
Solution
Here are query examples for the original_story_points custom field:
Query for PostgreSQL:
SELECT
ep.entity_id AS issue_id,
CAST((CAST(ep.json_value AS json) ->> 'original_story_points') AS float) AS original_story_points,
TIMESTAMP WITH TIME ZONE 'epoch' + CAST(CAST(ep.json_value AS json) ->> 'baseline_start' AS bigint) * INTERVAL '1 millisecond' AS baseline_start,
TIMESTAMP WITH TIME ZONE 'epoch' + CAST(CAST(ep.json_value AS json) ->> 'baseline_end' AS bigint) * INTERVAL '1 millisecond' AS baseline_end,
CONCAT(p.pkey, '-', i.issuenum) AS issue_key
FROM
entity_property ep
JOIN jiraissue i ON i.id = ep.entity_id
JOIN project p ON p.id = i.project
WHERE
ep.property_key = 'jpo-issue-properties'Query for MySQL:
SELECT
ep.entity_id AS issue_id,
CONVERT(ep.json_value ->> '$.original_story_points', decimal) AS original_story_points,
FROM_UNIXTIME(CAST((ep.json_value ->> '$.baseline_start') AS unsigned ) / 1000) as baseline_start,
FROM_UNIXTIME(CAST((ep.json_value ->> '$.baseline_end') AS unsigned ) / 1000) as baseline_end,
CONCAT(p.pkey, '-', i.issuenum) AS issue_key
FROM
entity_property ep
JOIN jiraissue i ON i.id = ep.entity_id
JOIN project p ON p.id = i.project
WHERE
ep.property_key = 'jpo-issue-properties'Query for MS SQL Server:
SELECT
ep.entity_id AS issue_id,
CAST(JSON_VALUE(ep.json_value, '$.original_story_points') AS FLOAT) AS original_story_points,
DATEADD(SS, CAST(JSON_VALUE(ep.json_value, '$.baseline_start') AS BIGINT)/1000, '1970/1/1') AS baseline_start,
DATEADD(SS, CAST(JSON_VALUE(ep.json_value, '$.baseline_end') AS BIGINT)/1000, '1970/1/1') AS baseline_end,
CONCAT(p.pkey, '-', i.issuenum) AS issue_key
FROM
entity_property ep
JOIN jiraissue i ON i.id = ep.entity_id
JOIN project p ON p.id = i.project
WHERE
ep.property_key = 'jpo-issue-properties'