23 Full-Text Search Queries
You can use Oracle SQL condition json_textcontains
in a
CASE
expression or the WHERE
clause of a
SELECT
statement to perform a full-text search of JSON data. You
can use PL/SQL procedure CTX_QUERY.result_set
to perform facet
search over JSON data.
- Oracle SQL Condition JSON_TEXTCONTAINS
You can use Oracle SQL conditionjson_textcontains
in aCASE
expression or theWHERE
clause of aSELECT
statement to perform a full-text search of JSON data. - JSON Facet Search with PL/SQL Procedure CTX_QUERY.RESULT_SET
If you have created a JSON search index then you can also use PL/SQL procedureCTX_QUERY.result_set
to perform facet search over JSON data. This search is optimized to produce various kinds of search hits all at once, rather than, for example, using multiple separate queries with SQL functioncontains
.
Parent topic: Query JSON Data
23.1 Oracle SQL Condition JSON_TEXTCONTAINS
You can use Oracle SQL condition json_textcontains
in a
CASE
expression or the WHERE
clause of a
SELECT
statement to perform a full-text search of JSON
data.
Oracle Text technology underlies condition
json_textcontains
. This condition acts like SQL function
contains
when the latter uses parameter
INPATH
. The syntax of the search-pattern argument of
json_textcontains
is the same as that of SQL function
contains
. This means, for instance, that you can query for text
that is near some other text, or query use fuzzy pattern-matching. If the
search-pattern argument contains a character or a word that is reserved with
respect to Oracle Text search then you must escape that character or
word.
To be able to use condition json_textcontains
you must
first do one of the following; otherwise, an error is raised when you use
json_textcontains
. (You cannot do both — an error is raised if
you try.)
-
Create a JSON search index for the JSON column.
-
Store the column of JSON data to be queried in the In-Memory Column Store (IM column store), specifying keyword
TEXT
. The column must of data typeJSON
; otherwise an error is raised. (JSON
type is available only if database initialization parametercompatible
is at least20
.)
Note:
By default, a JSON search index supports case-insensitive searching. To enable or
disable case-sensitive indexing, use the mixed_case
attribute of
the BASIC_LEXER
preference when creating the index. See BASIC_LEXER in Oracle Text
Reference.
Note:
Oracle SQL function json_textcontains
provides powerful
full-text search of JSON data. If you need only simple string pattern-matching then
you can instead use a path-expression filter condition with any of these
pattern-matching comparisons: has substring
, starts
with
, like
, like_regex
, or
eq_regex
.
Example 23-1 shows a full-text query that finds purchase-order documents that
contain the keyword Magic
in any of the line-item part
descriptions.
You can order the results returned by json_textcontains
according to their search-hit relevance, by passing an optional scoring-label
argument and using ORDER BY SCORE
with that same label number.
Example 23-2 illustrates this.
See Also:
-
Oracle Database SQL Language Reference for information about Oracle SQL condition
json_textcontains
. -
Oracle Text CONTAINS Query Operators in Oracle Text Reference for complete information about Oracle Text
contains
operator. -
Special Characters in Oracle Text Application Developer's Guide for information about configuring a JSON search index to index documents with special characters.
-
Special Characters in Oracle Text Queries in Oracle Text Reference for information about the use of special characters in SQL function
contains
search patterns (and hence injson_textcontains
search patterns). -
Reserved Words and Characters in Oracle Text Reference for information about the words and characters that are reserved with respect to Oracle Text search, and Escape Characters in Oracle Text Reference for information about how to escape them.
-
CONTAINS SQL Example in Oracle Text Application Developer's Guide for an example of using
SCORE
with SQL functionCONTAINS
. -
SCORE in Oracle Text Reference.
Example 23-1 Full-Text Query of JSON Data with JSON_TEXTCONTAINS
SELECT po_document FROM j_purchaseorder
WHERE json_textcontains(po_document,
'$.LineItems.Part.Description',
'Magic');
Example 23-2 JSON_TEXTCONTAINS: Sorting Query Results By Relevance Using SCORE
This query selects the PO numbers of purchase orders whose descriptions contain the
text run
. It orders the results by relevance using an optional
scoring-label argument. The query returns also the relevance score for each purchase
order.
The scoring label passed to json_textcontains
must be the same as
the label used with SCORE
. In this case the label is
1
.
The first 17 purchase orders listed have score 18; the remaining 85 purchase
orders have score 9. The former group match pattern run
better than the latter (they match it twice per purchase order instead of once).
SELECT po.po_document.PONumber, SCORE(1)
FROM j_purchaseorder po
WHERE json_textcontains (po.po_document,
'$.LineItems.Part.Description',
'run',
1)
ORDER BY SCORE(1) DESC;
Results (some elided):
PONUMBER SCORE(1)
-------- --------
1 18
9958 18
...
1388 18
36 9
22 9
...
8637 9
102 rows selected.
23.2 JSON Facet Search with PL/SQL Procedure CTX_QUERY.RESULT_SET
If you have created a JSON search index then you can also use PL/SQL
procedure CTX_QUERY.result_set
to perform facet search over JSON
data. This search is optimized to produce various kinds of search hits all at once, rather
than, for example, using multiple separate queries with SQL function
contains
.
To search using procedure CTX_QUERY.result_set
you pass it
a result set descriptor (RSD), which specifies (as a JSON
object with predefined operator fields $query
,
$search
, and $facet
) the JSON values you want to find
from your indexed JSON data, and how you want them grouped or aggregated. The values you
can retrieve and act on are either JSON scalars or JSON arrays of scalars.
(Operator-field $query
is also used in SODA query-by-example
(QBE) queries. You can use operator $contains
in the value of field
$query
for full-text matching similar to that provided by Oracle
SQL condition json_textcontains
.)
The RSD fields serve as an ordered template, specifying what to include in the output result set. (In addition to the found JSON data, a result set typically includes a list of search-hit rowids and some counts.)
A $facet
field value is a JSON array of facet objects, each
of which defines JSON data located at a particular path and perhaps satisfying some
conditions, and perhaps an aggregation operation to apply to that data.
You can aggregate facet data using operators $count
,
$min
, $max
, $avg
, and
$sum
. For example, $sum
returns the sum of the
targeted data values. You can apply an aggregation operator to all scalar values
targeted by a path, or you can apply it separately to buckets
of such values, defined by different ranges of values.
Finally, you can obtain the counts of occurrences of distinct values at a
given path, using operator $uniqueCount
.
For example, consider this $facet
value:
[ {"$uniqueCount" : "zebra.name"},
{"$sum" : {"path" : "zebra.price",
"bucket : [{"$lt" : 3000},
{"$gte" : 3000}]},
{"$avg" : "zebra.rating"} ]
When query results are returned, the value of field $facet
in the output is an array of three objects, with these fields:
-
zebra.name
— The number of occurrences of each zebra name. -
zebra.price
— The sum of zebra prices, in two buckets: prices less than 3000 and prices at least 3000. -
zebra.rating
— The average of all zebra ratings. (Zebras with no rating are ignored.)
[ {"zebra.name" : [ {"value":"Zigs",
"$uniqueCount:2},
{"value":"Zigzag",
"$uniqueCount:1},
{"value":"Storm",
"$uniqueCount:1} ]},
{"zebra.price" : [ {"value":1000,
"$uniqueCount:2},
{"value":3000,
"$uniqueCount:2},
{"value":2000,
"$uniqueCount:1} ]},
{"zebra.rating" : {"$avg":4.66666666666666666667}} ]
Related Topics
Parent topic: Full-Text Search Queries