Azure DevOps Querying all PBIs in an Epic

Question

I try to query the work item data to get a flat list of PBIs that belong to a given Epic with a parent relation ship Epic > Feature > PBI. Wiql seems to be limited in this regard, I can't find a "join" clause.

In SQL, I'd do it somehow like this (pseudo script):

select P.*
from Epic E
    inner join Feature F on E.Id = F.EpicId
    inner join Pbi P on F.Id = P.FeatureId
where E.Title = 'Project 1'

The background is to create a shared query (or even using the API) and build a dashboard chart to see the number and states of the PBIs in a given Epic.

Any idea how I can achieve this kind of query?

Answer 1

We can list all the child items in an Epic instead of all PBIs, please use WIQL below to get all child item from a specific parent work item(ID):

SELECT
    [System.Id],
    [System.WorkItemType],
    [System.Title],
    [System.AssignedTo],
    [System.State],
    [System.Tags]
FROM workitemLinks
WHERE
    (
        [Source].[System.TeamProject] = @project
        AND [Source].[System.WorkItemType] = 'Epic'
        AND [Source].[System.State] <> ''
        AND [Source].[System.Id] = {Epic ID}
    )
    AND (
        [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'
    )
    AND (
        [Target].[System.TeamProject] = @project
        AND [Target].[System.WorkItemType] = 'Product Backlog Item'
    )
ORDER BY [System.Id]
MODE (Recursive)

As a workaround, when we add child item(PBI) to an epic, we can add a same tag and get the result via query tag, then we can create a dashboard chart to see the number and states of the PBIs in a given Epic.