7.15.37. vector_find

7.15.37.1. Summary

Added in version 8.0.4.

It returns the first element that matches the given condition from the given vector. If no element is found, it returns null.

You can use not only equal condition but also less than condition, prefix equal condition and so on.

To enable this function, register functions/vector plugin by the following command:

plugin_register functions/vector

7.15.37.2. Syntax

vector_find has two or three parameters:

vector_find(vector, value)
vector_find(vector, value, mode)

If you omit the third argument, each element in the vector is compared with value by equality comparison.

7.15.37.3. Usage

You need to register functions/vector plugin at first:

Execution example:

plugin_register functions/vector
# [[0,1337566253.89858,0.000355720520019531],true]

Here is a schema definition and sample data.

Sample schema:

Execution example:

table_create  Memos TABLE_HASH_KEY ShortText
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Memos tags COLUMN_VECTOR ShortText
# [[0,1337566253.89858,0.000355720520019531],true]

Sample data:

Execution example:

load --table Memos
[
{"_key": "Groonga is fast",          "tags": ["groonga"]},
{"_key": "Mroonga is fast",          "tags": ["mroonga", "groonga"]},
{"_key": "Groonga is very good!",    "tags": ["groonga"]},
{"_key": "Droonga is fast",          "tags": ["droonga", "groonga"]},
{"_key": "Groonga is a HTTP server", "tags": ["groonga", "http"]}
]
# [[0,1337566253.89858,0.000355720520019531],5]

Here is a simple usage of vector_find that searches an element in tags column and returns the first found element:

Execution example:

select \
  --table Memos \
  --output_columns 'tags, vector_find(tags, "mroonga")'
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         5
#       ],
#       [
#         [
#           "tags",
#           "ShortText"
#         ],
#         [
#           "vector_find",
#           null
#         ]
#       ],
#       [
#         [
#           "groonga"
#         ],
#         null
#       ],
#       [
#         [
#           "mroonga",
#           "groonga"
#         ],
#         "mroonga"
#       ],
#       [
#         [
#           "groonga"
#         ],
#         null
#       ],
#       [
#         [
#           "droonga",
#           "groonga"
#         ],
#         null
#       ],
#       [
#         [
#           "groonga",
#           "http"
#         ],
#         null
#       ]
#     ]
#   ]
# ]

It returns "mroonga" when the tags column value includes "mroonga" element. It returns null otherwise.

You can customize how to compare with each value by the third argument. Here is a usage to use full text search to find an element:

Execution example:

select \
  --table Memos \
  --output_columns 'tags, vector_find(tags, "roonga", "@")'
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         5
#       ],
#       [
#         [
#           "tags",
#           "ShortText"
#         ],
#         [
#           "vector_find",
#           null
#         ]
#       ],
#       [
#         [
#           "groonga"
#         ],
#         "groonga"
#       ],
#       [
#         [
#           "mroonga",
#           "groonga"
#         ],
#         "mroonga"
#       ],
#       [
#         [
#           "groonga"
#         ],
#         "groonga"
#       ],
#       [
#         [
#           "droonga",
#           "groonga"
#         ],
#         "droonga"
#       ],
#       [
#         [
#           "groonga",
#           "http"
#         ],
#         "groonga"
#       ]
#     ]
#   ]
# ]

It returns "groonga", "mroonga" or "droonga" when the tags column value includes one of them. The returned value is the first found element. For example, "droonga" is returned for ["droonga", "groonga"]. "groonga" isn’t returned because the first element "droonga" is found before the second element "groonga" is searched.

It returns null when tags column value doesn’t include them.

7.15.37.4. Parameters

It requires two parameters.

There is an optional parameter.

7.15.37.4.1. Required parameters

vector and value are required.

7.15.37.4.1.1. vector

Specifies a vector value to be searched an element.

7.15.37.4.1.2. value

Specifies a value to be compared.

7.15.37.4.2. Optional parameters

mode is optional.

7.15.37.4.2.1. mode

Specifies a mode that specifies how to compare each element with value.

See default_mode for available mode names. All mode names except "NEAR", "SIMILAR" and "SUFFIX" are supported.

The default mode is "EQUAL". Note that default_mode says the default mode is "MATCH" but the default mode of vector_find is "EQUAL".

7.15.37.5. Return value

The matched element on match, null otherwise.