...

SELECT
    * 
FROM
    fragment 
WHERE
    parent_id IN (
        SELECT
            id 
        FROM
            fragment 
        WHERE
            xpath_component = 'categories[@code=''1'']'
            AND parent_id = (
                SELECT
                    id 
                FROM
                    fragment 
                WHERE
                    xpath_component = 'bookstore'
                    AND anchor_id = 3
                    AND parent_id IS NULL
            )
        ) 
        AND (
            xpath_component = 'books'
            OR xpath_component LIKE 'books[%'
        ) 
        AND (
            attributes @> '{"title":"Matilda"}'
        )

A note on fetching descendant nodes

The above sections only partly describe the current and proposed solutions. In both cases, a CPS path query is actually send as two database queries:

• the first query performs the CPS path query
• the second query fetches the descendants, if required
  • alternatively, if an ancestor CPS path was provided, the second query would fetch the ancestors (with descendants if required)

The current implementation uses a regex-based query to fetch descendants, while the proposed solution will use recursive SQL to fetch descendants of fragment returned from the CPS path query:

WITH RECURSIVE descendant_search AS (
  SELECT id, 0 AS depth
    FROM fragment
   WHERE id IN (:fragmentIds)
   UNION
  SELECT child.id, depth + 1
    FROM fragment child INNER JOIN descendant_search ds ON child.parent_id = ds.id
   WHERE depth <= :maxDepth
) 
SELECT f.id, anchor_id AS anchorId, xpath, f.parent_id AS parentId, CAST(attributes AS TEXT) AS attributes 
FROM fragment f INNER JOIN descendant_search ds ON f.id = ds.id

Proof of Concept

A PoC was developed so that performance could be compared against existing Cps Path Query algorithms.

...

/openroadm-devices/openroadm-device[@status="success"]

In above cases, it is observed that the existing algorithm grows quadratically - O(n²), while the PoC algorithm grows linearly - O(n).

...