...

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 this Cps 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, and also allow GET operation (getDataNodes) to use index-only lookups while supporting lists.
• Storing "books[@title='Matilda']" will allow for future optimization of queries to skip the attribute check, for cases where the leaf-condition references the key leaf (as defined in the Yang model).

A note on fetching descendant nodes

...