The solution is to create intermediate views that make it easy to access the required information.
The first step is to create a view containing the left and right side of each relationship in one row:
create view relationship_source_target as
select rr_left.id as source_role_id,
rr_left.role_docref as source_docref,
rr_right.id as target_role_id,
rr_right.role_docref as target_docref
from relationship_role rr_left
join relationship_link rl on rr_left.relationship_id = rl.id
join (select m.id,
rmr.role->>'role' as role_name,
rmr.role_index
from model m,
jsonb_array_elements(m.content::jsonb->'content'->'entityCharacteristics') with ordinality as rmr(role, role_index)
where m.content::jsonb->'header'->>'modelType' = 'relationship') as rmr on rmr.id = rl.relationship_model and rr_left.role_name = rmr.role_name and rmr.role_index = 1
join relationship_role rr_right on rr_left.relationship_id = rr_right.relationship_id and rr_left.id <> rr_right.id;
Based on this view a second view is created containing the transitive relations
create view transitive_relationship_source_target as
with recursive transitive_relationship as (
select rst.source_role_id,
rst.source_docref,
rst.target_role_id,
rst.target_docref,
null::bigint as previous_role_id
from relationship_source_target rst
union
select tr.source_role_id as source_role_id,
tr.source_docref as source_docref,
rst.target_role_id as target_role_id,
rst.target_docref as target_docref,
tr.target_role_id as previous_role_id
from transitive_relationship tr
join relationship_source_target rst on tr.target_docref = rst.source_docref
)
select tr.source_role_id,
tr.source_docref,
tr.target_role_id,
tr.target_docref,
tr.previous_role_id
from transitive_relationship tr;
The previous_role_id column in this view also allows to track different paths to the same document via some relationships.
The one can use the following sql to see all of the documents that are somehow related to FooDM/1234-5678-90ab-cdef:
select tr.target_docref
from transitive_relationship_source_target tr
where tr.source_docref = 'FooDM/1234-5678-90ab-cdef'
Note that those views may conflict with database updates in future dataservices releases and should therefore be dropped before attempting to deploy any updates.