Page History

...

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"}'
        )

Design decision: should xpath_component contain the leaf key?

Given a data node with this unique path:

/bookstore/categories[@code="1"]/books[@title="Matilda"]

Is it better to store the xpath_component as "books[@title='Matilda']" or simply "books"? Further study is needed to determine performance impact of this decision.

• Storing only 'books' will allow for removing the 'LIKE' operator from the SQL query, potentially speeding queries in the general case.
  With further development, this could allow for GET operation (getDataNodes) to return whole lists, using an index-only lookup. (However, given how fast the proposed query solution is, the existence of the GET operation is questionable.)
• Storing "books[@title='Matilda']" will allow for optimization of queries where the leaf-condition references the key leaf (as defined in the Yang model), thus skipping both the LIKE operator and the attribute check, but only in this specific case. See section "Index-only lookup where leaf-condition is in the xpath" of this document.

Choosing 'books' in this case would affect not only the processing of the last path component, but all path components, requiring leaf-condition/attribute lookups at each path-component test, degrading performance in all use-cases.
Thus, "books[@title='Matilda']" is the only acceptable choice for xpath_component.

A note on fetching descendant nodes

...