Supported Cypher Syntax

This page documents the supported openCypher Syntax that BloodHound officially supports

Purpose

This article describes how to use Cypher Search within BloodHound. Users of BloodHound should use it to extend the basic search functionality of BloodHound.

Supported Query Components

Below are the currently supported openCypher query components translated by CySQL.

Pattern Matching

Node Matching

match (n)
where n.name = 'my name'
return n

Inline Node Label Matchers

match (n:User)
return n

Inline Node Pattern Property Matchers

match (n {prop: 'value'})
return n

Relationship Matching

match (:User)-[r]->()
return r

match (:User)-[r:MemberOf|GenericWrite]->()
return r

Recursive Expansion

match (:User)-[r:MemberOf*..]->(:Group)
return r

Ranges for Recursive Expansion

match (:User)-[r:MemberOf*2..]->(:Group)
return r

match (:User)-[r:MemberOf*2..4]->(:Group)
return r

Pattern Projections

match p = (:Computer)-[:HasSession]->(:User)
return p

match p = (:User)-[:MemberOf*..]->(:Group)
return p

Multiple Reading Clauses

match (u:User) where u.is_eligible
match p = (u)<-[:HasSession]-(c:Computer)
return p

Shortest Paths

match p = shortestPath((u:User)-[*..]->(:Domain))
where u.objectid = 'UUID-1234-567890'
return p

All Shortest Paths

match p = allShortestPaths((u:User)-[*..]->(:Domain))
where u.objectid = 'UUID-1234-567890'
return p

Build multi-part queries

Use WITH to pass intermediate results from one query part to the next. You can use WITH to filter, aggregate, sort, or alias results before continuing the query.

MATCH p = shortestPath((u:User)-[:MemberOf*1..]->(g:Group))
WITH DISTINCT g AS targetGroup, COUNT(u) AS userCount
RETURN targetGroup.name, userCount
ORDER BY userCount DESC
LIMIT 5

Result Ordering and Pagination

ORDER BY

Use ORDER BY with RETURN or WITH to sort results by a projected value. Ascending order is the default; append DESC for descending order.

match (u:User)
return u.name
order by u.name desc
limit 10

SKIP and LIMIT

Use SKIP and LIMIT with RETURN or WITH to paginate or trim a result set after sorting.

match (n:User)
return n
order by n.name
skip 10
limit 100

Entity Creation

You must enable the clause by setting enable_cypher_mutations: true in your BloodHound configuration file.

Create nodes

create (n:User {name: 'alice', objectid: 'UUID-1234-567890'})
return n

Create relationships

create (u:User {name: 'alice'})-[r:MemberOf]->(g:Group {name: 'admins'})
return r

Entity Updates

You must enable the clause by setting enable_cypher_mutations: true in your BloodHound configuration file.

Setting Properties and Labels

match (n:Base)
where n.obviously_is_user
set n.other = 1
set n:User
return n

match ()-[r:HasSession]->(:User)
set r.special_property = true

Removing Properties and Labels

match (n:User)
remove n.name
remove n:User
return n

match ()-[r:HasSession]->(:User)
remove r.special_property

Entity Deletion

You must enable the clause by setting enable_cypher_mutations: true in your BloodHound configuration file.

match (s:User)
detach delete s

match ()-[r:MemberOf]->()
delete r

Unwind a list into rows

Expand a list into individual rows for further filtering, aggregation, or sorting.

match (u:User)
where u.name contains "A"
with collect(u.name) as temp
unwind temp as usernames
return usernames
limit 100

Queries that use UNWIND return tabular results only.

Supported Query Filters

Comparison Expressions

The following operators are supported in authoring comparison expressions:

• =
• <>
• <
• >
• <=
• >=

When authoring comparison statements, users must be aware of the typing requirements of CySQL compared to Cypher as executed by Neo4j.

For more information see the Differences between Cypher and CySQL subsection Stricter Typing Requirements.

Negation

Negation in query filters is supported with the not operator:

match (n:User)
where not(n.eligible)
return n

Conjunction and Disjunction

Conjunction and and disjunction or operators are both supported:

match (n:User)
where n.eligible and n.enabled
return n

match (n:User)
where n.eligible or n.seen_as_active
return n

String Searching

Searching strings may be performed in a variety of ways. These matches are case-sensitive and do not support wildcard expansions.

String Prefix Matching

A string property may be filtered by prefix matching:

match (n:User)
where n.name starts with 'my prefix'
return n

String Contains Matching

A string property may be filtered by contains matching:

match (n:User)
where n.details contains 'something interesting'
return n

String Suffix Matching

A string property may be filtered by suffix matching:

match (n:User)
where n.name ends with 'my suffix'
return n

Regular Expressions

match (n:User)
where n.name =~ 'userPrefix.*'
return n

Pattern Predicates

Query filters may also include pattern lookups. For example, searching for users with no active login sessions:

match (n:User)
where not((n)<-[:HasSession]-(:Computer))
return n

Quantifier Expressions

any

Returns true if at least one item in the list contains the specified value.

WITH "KEYWORD" as SPNKeyword
MATCH (n:User)
WHERE ANY(keyword IN n.serviceprincipalnames WHERE toUpper(keyword) CONTAINS toUpper(SPNKeyword))
RETURN n

single

Indicates that exactly one item in the list contains the specified value.

WITH "KEYWORD" as SPNKeyword
MATCH (n:User)
WHERE SINGLE(keyword IN n.serviceprincipalnames WHERE toUpper(keyword) CONTAINS toUpper(SPNKeyword))
RETURN n

none

Indicates that none of the objects in the list contain the specified value.

WITH "KEYWORD" as SPNKeyword
MATCH (n:User)
WHERE NONE(keyword IN n.serviceprincipalnames WHERE toUpper(keyword) CONTAINS toUpper(SPNKeyword))
RETURN n

all

Indicates that all objects in the list contain the specified value.

WITH "KEYWORD" as SPNKeyword
MATCH (n:User)
WHERE ALL(keyword IN n.serviceprincipalnames WHERE toUpper(keyword) CONTAINS toUpper(SPNKeyword))
RETURN n

Supported Subquery Expressions

collect

A collect subquery expression can be used to create a list with the rows returned by a given subquery.

count

Aggregates and counts the results of the given subquery as an integer.

Supported Cypher Functions

duration Function

Parses a valid duration string into a time duration that can be used in conjunction with other duration or date types.

match (s)
where s.created_at = date() - duration('P1D')
return s

id Function

Returns the entity identifier of the node or relationship.

match (s)
where id(s) in [1, 2, 3, 4]
return s

localtime

Returns the local time without timezone information.

match (s)
where s.created_at <= localtime()
return s

localdatetime

Returns the local datetime without timezone information.

match (s)
where s.created_at > localdatetime()
return s

date

Returns the current date with timezone information.

match (s)
where s.created_at = date()
return s

datetime

Returns the current datetime with timezone information.

match (s)
where s.created_at < datetime()
return s

type

Returns the type of the given relationship reference. This function returns the relationship’s type as a text value. Type checks utilizing this function will not be index accelerated and may exhibit poor performance.

match ()-[r]->()
where type(r) = 'EdgeKind1'
return r

split

Takes a given expression and text delimiter and returns a text array containing split components, if any. If the given expression does not evaluate to a text value this function will raise an error.

match (u:User)
where '255' in split(u.ip_addr, '.')
return u

tolower

Returns the localized lower-case variant of a given expression. If the given expression does not evaluate to a text value this function will raise an error.

match (u:User)
return tolower(u.name)

toupper

Returns the localized upper-case variant of a given expression. If the given expression does not evaluate to a text value this function will raise an error.

match (u:User)
return toupper(u.name)

tostring

Returns the text value of a given expression. If the given expression represents a type that can not be converted to text this function will raise an error.

match (u:User)
return tostring(u.num_active_logins)

toint

Returns the integer value of a given expression. If the given expression represents a type that can not be converted or parsed to an integer this function will raise an error.

match (u:User)
return toint(u.integer_in_text_property)

coalesce

Returns the first non-null value in a list of expressions. This is critically useful for navigating differences in null behavior between Cypher and CySQL.

match (n:NodeKind1)
where n.target = coalesce(n.a, n.b, 'last_resort')
return n

size

Returns the number of items in an expression that evaluates to any array type.

match (n:NodeKind1)
where size(n.array_value) > 0
return n

Caveats

The size function is expected to behave differently if the given expression evaluates to a text value. In this case, the function returns the number of Unicode characters present in the text value. This behavior is currently not supported in CySQL translation.

nodes

Returns an ordered list of all nodes in a matched path.

match p = ()-[]->()
return nodes(p)

relationships

Returns the ordered relationship list for a path.

match p = ()-[]->()
return relationships(p)

startNode

Returns the start node for a relationship reference.

match p = (a)-[*1..]->(b)
where none(r in relationships(p) where startNode(r).name = 'blocked')
return p

endNode

Returns the end node for a relationship reference.

match ()-[r]->() return endNode(r)

head

Returns the first element of a list.

match p = ()-[]->()
return head(nodes(p))

tail

Returns all elements of a list except the first.

match p = ()-[]->()
return tail(nodes(p))

Known Defects in Supported Components

The below issues are known defects. They are classified as defects of CySQL as the intent is to correctly support their use.

labels Function

Returns the labels of the given node reference. This function returns the node’s labels as a text array value. Label checks utilizing this function will not be index accelerated and may exhibit poor performance.

match (n)
where 'User' in labels(n)
return n

Caveats

While currently implemented this function returns the smallint array of labels associated with a node when referenced in CySQL. Future support to convert the smallint array of node labels into text values is planned.

Unpacking Arrays of Entities for Comparison

Arrays containing graph entities are not unpacked during comparisons:

match (n:User) where n.disabled
with collect(n) as disabled
match p = (:Computer)-[:HasSession]->(u:User)
where not u in disabled
return p limit 1

Queries that contain similar constructs will result in the following translation error: ERROR: column notation .id applied to type nodecomposite[], which is not a composite type (SQLSTATE 42809).

Right-Hand Bound Node Lookups

Patterns that utilize a bound reference in the right-hand node pattern will not correctly author the required SQL joins:

match (e)
match p = ()-[]->(e)
return p
limit 1

Queries that contain similar constructs will result in the following translation error: ERROR: invalid reference to FROM-clause entry for table "s0" (SQLSTATE 42P01).

Untyped Array References and Literals

Untyped array references, including empty arrays, fail to pass type inference checks in CySQL. Support for additional type hinting and inference is required to better support these use-cases.

match (n:User)
where n.auth_modes = []
return n

Queries that contain similar constructs will result in the following translation error: Error: array literal has no available type hints.

Unsupported Constructs

Below are constructs of the Cypher language that did not make the 1.0 definition of the CySQL specification. Future efforts may be pursued to add support for these language features.

• XOR Operations
• Case Expressions
• List Comprehensions
• Pattern Comprehensions
• Existential Subqueries (e.g. exists)
• Merge Statements
• Pattern Predicates using Recursive Expansion

Differences between Cypher and CySQL

Translating Cypher to SQL via CySQL comes with a few semantic differences that users should be aware of.

Stricter Typing Requirements

SQL comparisons are stricter than comparisons executed in Neo4j. Some of these typing constraints are handled automatically by CySQL, however, some type mismatches do make it down to the underlying SQL database.

Given the Cypher query: match (n:User) where n.name = 123 return n limit 1;

The translated SQL, when executed, results in the following error: Error: ERROR: invalid input syntax for type bigint: "[e-mail]" (SQLSTATE 22P02)

This indicates that there is a node with a value for n.name that is not parsable as an integer.

In the future, CySQL translation will cover most of the strict typing requirements automatically for users.

Index Utilization

Indexing in CySQL does not require a label specifier to be utilized. If the node property name is indexed in CySQL, both:

match (n:User)
where n.name = '1234'
return n

and

match (n)
where n.name = '1234'
return n

will use the name index regardless of node label.

null Behavior

Behavior around null in SQL differs from how Neo4j executes Cypher. Certain expression operators in Neo4j’s implementation of Cypher will treat null differently than their SQL counterparts while some semantics are very similar.

Ideally, entity properties should strive to remove null as a conditional case as much as possible. In cases where this is not possible, users are advised to exercise the coalesce(...) function:

match (n:User)
where coalesce(n.name, '') contains '123'
return n
limit 1

Silent Query Failure

null can taint result sets and also further complicate future comparisons in the query:

match (n:User)
with n.name as n
where n = '123'
return 1

The reference n is being projected by the multipart with statement but this projection removes the resultset from the original query, allowing for ambiguity to slip into future operations against n.name where some values of n.name may be null.