Querying for Advanced Roadmaps for Jira Custom Fields
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
要約
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.
ソリューション
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'