Escaping characters in a solr_query
Solr queries require escaping special characters that are part of the query syntax.
| Character | Description |
|---|---|
+ |
Plus. Required search term operator. |
- |
Minus. Prohibited search term operator. |
&& |
Double ampersand. AND operator. Both terms either side of the operator are required for a match. |
|| |
Double pipe. OR operator. |
! |
Exclamation mark. NOT operator. |
( |
Left parenthesis |
) |
Right parenthesis |
" |
Double quote |
~ |
Tilde |
* |
Asterisk |
? |
Question mark |
: |
Colon |
To escape these characters, use a slash (\) before the character to escape.
For example, to search for a literal double quotation mark (") character, escape the " for Solr with \".
When using solr_query, you can escape special characters using either CQL Solr or JSON forms:
- CQL Solr
-
...WHERE solr_query='<field>:<value>' - JSON
-
WHERE solr_query='{ "q": "<field>:<value>"}'JSON-encoded queries require that values must also be JSON-escaped for special characters.
For queries that contain double quotation marks, use triple slashes \\\:
-
For query syntax: One slash
\to escape the" -
For the JSON string syntax: Two slashes
\\to escape the\Triple slashes
\\\escape both characters in\"to produce\\(an escaped escape) and\"(an escaped double quote).
Escaping single quotation marks
-
Double the single quotation mark (
**'**)- CQL
-
...WHERE solr_query='name:Walter**''**s' - JSON
-
...WHERE solr_query='{ "q": "Walter'**'**s"}'
-
Use dollar-quotes for the string constant
- CQL
-
...WHERE solr_query=****name:Walter's**** - JSON
-
...WHERE solr_query=****{ "q": "Walter's"}****
Escaping double quotation marks
- CQL
-
Double the single quotation mark (
**'**) and add the backslash (**\\**) for Solr escaping...WHERE solr_query='name:Walter**\\''**s' - JSON
-
Escape
**\\"**to**\\\\\\"**to escape both special characters for JSON...WHERE solr_query='{ "q": "Walter**\\\\\\"**s"}'
Exact and fuzzy query examples
- Exact phrase query
-
For a row that looks like this, with an email address that includes a double quotation mark
greenr"q@example.com:INSERT INTO users(id, email) VALUES(1, 'greenr"q@example.com')"
Perform a phrase query to search for the email address that is enclosed in double quotation marks:
SELECT * FROM users where solr_query = ' { "q": "*:*", "fq": "email:\"greenr\\\"q@example.com\""} '; - Fuzzy query
-
For a row that looks like this, with the same email address that includes a double quotation mark
greenr"q@example.com:select * from test.users where solr_query='{"q":"email:r\\\"q@example"}' ; id | email | solr_query ------+-------+------------------------------ 1 | greenr"q@example.com | null (1 rows)For a term query (fuzzy search) for all email addresses that include
r"q@example, remove the double quotation marks but retain triple quotation marks for the escaped double quotation character that is part of the email address:SELECT * FROM users where solr_query = ' { "q": "*:*", "fq": "email:r\\\"q@example"} ';
Using JSON with solr_query requires additional syntax for literal characters that are Lucene special characters.
