To do this in JCR-SQL2, you have to declare a selector (e.g., table) for both the parent node and the child of that parent, and you have to use a join to constrain that the child node is actually a child of the parent. Now, I'm not sure whether the wildcard in the LIKE clause you used wanted the parent node to be directly under '/users/admin/send' or some descendant under '/users/admin/send'.

A query that only uses the standard grammar that does the latter is:

SELECT parent.* FROM [nt:unstructured] AS parent
  JOIN child ON ISCHILDNODE(child,parent)
 WHERE ISDESCENDANTNODE('/users/admin/send',parent)
   AND NAME(child) = 'user4'

Thus a node at '/users/admin/send/a/b/c' might satisfy the criteria as long as there was also a node at '/users/admin/send/a/b/c/user4'.

If, however, you did not want to allow the parent to be anywhere below the '/users/admin' node, then you should use an ISCHILDNODE constraint instead:

SELECT parent.* FROM [nt:unstructured] AS parent
  JOIN child ON ISCHILDNODE(child,parent)
 WHERE ISCHILDNODE('/users/admin/send',parent)
   AND NAME(child) = 'user4'

Note these queries are basically the same except on line 3.

BTW, ModeShape's implementation does support a few extra functions, so you could instead do this:

SELECT parent.* FROM [nt:unstructured] AS parent
  JOIN child ON ISCHILDNODE(child,parent)
 WHERE PATH(parent) LIKE '/users/admin/send/%'
   AND NAME(child) = 'user4'

The problem with this approach is that its hard to remember/know whether the wildcard will apply just to a single path segment or to all paths. Anyone reading the query has to know the behavior. (Really, this query should be identical to the first query I listed above, since LIKE is not really aware of path segments and simply treats the path as a string.) Because this query is not as explicit, I'd recommend not using it. (The "PATH" function was added because it can be used with other comparison operators, such as <, >, <=, and >=, which can't be done any other way.)

Hope this helps.