FIND
The DBMS_SEARCH.FIND procedure retrieves a hitlist, and facets an aggregations of JSON documents based on the specified query-by-example (QBE) filter conditions.
You can compute aggregations on different fields of the JSON data. The query lists search results in the JSON Results Set Interface, which supports faceted navigation and aggregations.
Syntax
DBMS_SEARCH.FIND(
index_name VARCHAR2,
search_QBE JSON);index_name
Specify name of the index on which you want to perform the query.
search_QBE
Specify the result_set_descriptor parameter value in JSON. It describes what the result set should contain.
The JSON format input result set descriptor consists of the $query, $search, and $facet parts:
{
"$query":text query and filter conditions,
"$search":search result specifications,
"$facet":faceted result specifications
}For details on each of these JSON objects, see The JSON Format Input Result Set Descriptor.
The JSON format output result set descriptor consists of the following parts:
"$count":number
"$hit":[ hit_object_1, ..., hit_object_i , ... ]
"$facet":[ facet_object_1, ..., facet_object_i, ...]For details on each of these JSON objects, see The JSON Format Result Set Output.
Example
Create a table and populate it with values:
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
connect sys/knl_example as sysdba;
Connected.
grant connect,resource, unlimited tablespace, ctxapp
to u1 identified by u1;
Grant succeeded.
connect u1/u1;
Connected.
create table tbl(id number primary key, jsn_col clob check(jsn_col is json));
Table created.
INSERT INTO tbl
VALUES (1,'{ "zebra" : { "price" : [2000,1000],
"name" : "Marty",
"stripes" : ["Dark","Light"],
"handler" : "Bob", "sold" : true }}');
1 row created.
INSERT INTO tbl
VALUES (2,'{ "zebra" : { "rating": 5, "price" : 1000,
"name" : "Zigby",
"stripes" : ["Light","Grey"],
"handler" : "Handy Marty", "sold" : "true" }}');
1 row created.
INSERT INTO tbl
VALUES (3,'{ "zebra" : { "rating": 4.5, "price" : 3000,
"name" : "Zigs",
"stripes" : ["Grey","Dark"],
"handler" : "Handy Marty", "sold" : false }}');
1 row created.
INSERT INTO tbl
VALUES (4,'{ "zebra" : { "rating": "4.5", "price" : "3000",
"name" : "Zigs",
"stripes" : ["Grey","Dark"],
"handler" : "Handy Marty", "sold" : null }}');
1 row created.
commit;
Commit complete.Create a DBMS_SEARCH index using the DBMS_SEARCH.CREATE_INDEX procedure, and add a source table to the index:
SQL> exec DBMS_SEARCH.CREATE_INDEX('JIDX');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SEARCH.ADD_SOURCE('JIDX','TBL');
PL/SQL procedure successfully completed.
SQL>Run the DBMS_SEARCH.FIND procedure:
Query: All zebras having name starting with Zig or having name Marty and
having a price greater than equal to 2000
Facets: For all zebras that satisfy the query, do the following
-- 1. Get the count of zebras per zebra handler
-- 2. Get the minimum zebra rating
-- 3. Get the count of zebras for each unique stripe color
select DBMS_SEARCH.FIND('JIDX',JSON('
{
"$query": { "$and" : [
{ "U1.TBL.JSN_COL.zebra.name" : { "$contains" : "Zig% or Marty" } },
{ "U1.TBL.JSN_COL.zebra.price" : { "$gte" : 2000 } }
]
},
"$facet" : [
{ "$uniqueCount" : "U1.TBL.JSN_COL.zebra.handler" },
{ "$min" : "U1.TBL.JSN_COL.zebra.rating" },
{ "$uniqueCount" : "U1.TBL.JSN_COL.zebra.stripes" }
]
}'));The output is as follows:
FIND_RESULT
--------------------------------------------------------------------------------
{
"$count" : 3,
"$facet" :
[
{
"U1.TBL.JSN_COL.zebra.handler" :
[
{
"value" : "Handy Marty",
"$uniqueCount" : 2
},
{
"value" : "Bob",
"$uniqueCount" : 1
}
]
},
{
"U1.TBL.JSN_COL.zebra.rating" :
{
"$min" : 4.5
}
},
{
"U1.TBL.JSN_COL.zebra.stripes" :
[
{
"value" : "Dark",
"$uniqueCount" : 3
},
{
"value" : "Grey",
"$uniqueCount" : 2
},
{
"value" : "Light",
"$uniqueCount" : 1
}
]
}
]
}
1 row selected.
connect sys/knl_example as sysdba;
Connected.
drop user u1 cascade;
User dropped.Related Topics