search

On this page

Searches text and nested values for words in a query.

Syntax

search(
    <data>,
    query => <query>
    [, analyzer => <analyzer>]
    [, mode => <mode>]
)

Arguments

ArgumentTypeDescription
<data>string, array, object, or anyValue to search. Nested arrays and objects are searched recursively.
querystringConstant. Search terms. Must not be NULL.
analyzerstringOptional constant; defaults to 'log'. Search analyzer. The supported value is 'log'. Must not be NULL.
modestringOptional constant; defaults to 'and'. Matching mode: 'and' or 'phrase'. Must not be NULL.

Returns

Returns boolean: TRUE if the analyzed query matches, otherwise FALSE. Returns NULL if <data> is NULL.

The analyzer splits the query and input text into lowercase tokens. The matching mode determines how they are compared:

  • 'and': all query tokens must occur, in any order. Tokens can match different strings in a nested value.
  • 'phrase': tokens must occur next to each other, in query order, within one string.

Use tokenize to inspect the tokens. For example, 'timeout' and 'timed out' produce different tokens and do not match each other.

Examples

Find a failure inside flexible event payloads

Search nested event fields without knowing which producer stores the failure message at which path.

VALUES
    ('evt-17', {'channel': 'web', 'error': {'message': 'Payment timeout at gateway'}}),
    ('evt-42', {'channel': 'mobile', 'messages': ['Payment accepted', 'Receipt sent']}),
    ('evt-63', {'channel': 'mobile', 'detail': 'Timeout while processing payment'})
SELECT $0 AS event_id, $1 AS payload
WHERE search(payload, query => 'payment timeout')
SELECT event_id, payload['channel']::string AS channel
ORDER BY event_id
+----------+---------+
| event_id | channel |
+----------+---------+
| evt-17   | web     |
| evt-63   | mobile  |
+----------+---------+

Both matching events contain payment and timeout, even though their messages use different fields and word order.

Distinguish token and phrase matching

Compare a broad failure search with an exact token sequence in customer messages.

VALUES
    ('Payment timeout at checkout'),
    ('Payment gateway timeout'),
    ('Timeout during payment'),
    ('Payment accepted')
SELECT $0 AS message
SELECT
    message,
    search(message, query => 'payment timeout') AS all_tokens,
    search(message, query => 'payment timeout', mode => 'phrase') AS phrase
+-----------------------------+------------+--------+
| message                     | all_tokens | phrase |
+-----------------------------+------------+--------+
| Payment timeout at checkout | true       | true   |
| Payment gateway timeout     | true       | false  |
| Timeout during payment      | true       | false  |
| Payment accepted            | false      | false  |
+-----------------------------+------------+--------+