ADD_PATH

Adds a path to a path-list preference for use with the PATHLIST parameter, when creating a path-subsetting JSON search index.

You use this parameter with the PL/SQL subprogram CTX_DDL.CREATE_PATH_LIST to specify a subset of paths to include or exclude from indexing. In this way, you can choose to index only relevant paths for efficient search. The excluded fields are not indexed, and the JSON search index is not used for them when querying. Filtering out irrelevant paths from documents can reduce the amount of data indexed, thereby minimizing disk space, indexing costs, and the index creation or rebuild time.

Syntax

begin
  CTX_DDL.ADD_PATH(
    pref_name     IN    VARCHAR2,
    path_type     IN    VARCHAR2,
    path_string   IN    VARCHAR2
  );
end;

pref_name

Specify the name of the pathlist preference to which you want to add a subset of paths.

path_type

Specify the type of search to target for your JSON search index:

NUMBER: For numeric-value range search
TIMESTAMP: For date and time value range-search
VARCHAR2: For string-value range search
TEXT: For full-text and string-equality search

path_string

Specify a path to add to the specified pathlist preference.

Note: Currently, you cannot add multiple paths in a single path string. To add multiple paths, you must use the ADD_PATH parameter to specify each path.

For example:

BEGIN
  CTX_DDL.create_path_list('json_pl_incl', CTX_DDL.PATHLIST_JSON, CTX_DDL.PATHLIST_INCLUDE);
  CTX_DDL.add_path('json_pl_incl', 'TEXT',     '<path1>');
  CTX_DDL.add_path('json_pl_incl', 'TEXT',     '<path2>');
  CTX_DDL.add_path('json_pl_incl', 'NUMBER',   '<path3>');
END;
/

Examples

To create an INCLUDE pathlist:

Here, you first create a list of paths (json_pl_incl) to be included for indexing using the CTX_DDL.CREATE_PATH_LIST and CTX_DDL.ADD_PATH APIs. You then create an index using the PATHLIST parameter, whose value is a named list of the paths to be included.

This example creates a JSON search index for full-text and string-equality searches of fields $.SpecialInstructions and $.LineItems.Part.Description of a purchase order document. But it also indexes fields $.PONumber and $.LineItems.Part.UnitPrice for numeric-value ranges, and fields $.Reference, $.User, $.ShippingInstructions.name, and $.ShippingInstructions.Address.zipCode for string-value ranges.

-- create a pathlist preference and add paths

BEGIN
  CTX_DDL.create_path_list('json_pl_incl', CTX_DDL.PATHLIST_JSON, CTX_DDL.PATHLIST_INCLUDE);
  CTX_DDL.add_path('json_pl_incl', 'TEXT',     '$.SpecialInstructions');
  CTX_DDL.add_path('json_pl_incl', 'TEXT',     '$.LineItems.Part.Description');
  CTX_DDL.add_path('json_pl_incl', 'NUMBER',   '$.PONumber');
  CTX_DDL.add_path('json_pl_incl', 'NUMBER',   '$.LineItems.Part.UnitPrice');
  CTX_DDL.add_path('json_pl_incl', 'VARCHAR2', '$.Reference');
  CTX_DDL.add_path('json_pl_incl', 'VARCHAR2', '$.User');
  CTX_DDL.add_path('json_pl_incl', 'VARCHAR2', '$.ShippingInstructions.name');
  CTX_DDL.add_path('json_pl_incl', 'VARCHAR2', '$.ShippingInstructions.Address.zipCode');
END;
/

-- declare the pathlist preference name

CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc)
  FOR JSON PARAMETERS ('PATHLIST json_pl_incl');

To create an EXCLUDE pathlist:

Here, you first create a list of paths (json_pl_excl) to be excluded from indexing using the CTX_DDL.CREATE_PATH_LIST and CTX_DDL.ADD_PATH APIs. You then create an index using the PATHLIST parameter, whose value is a named list of the paths to be excluded.

-- create a pathlist preference and add paths

BEGIN
  CTX_DDL.create_path_list('json_pl_excl', CTX_DDL.PATHLIST_JSON, CTX_DDL.PATHLIST_EXCLUDE);
  CTX_DDL.add_path('json_pl_excl', 'TEXT',     '$.SpecialInstructions');
  CTX_DDL.add_path('json_pl_excl', 'TEXT',     '$.LineItems.Part.Description');
  CTX_DDL.add_path('json_pl_excl', 'NUMBER',   '$.PONumber');
  CTX_DDL.add_path('json_pl_excl', 'NUMBER',   '$.LineItems.Part.UnitPrice');
  CTX_DDL.add_path('json_pl_excl', 'VARCHAR2', '$.Reference');
  CTX_DDL.add_path('json_pl_excl', 'VARCHAR2', '$.User');
  CTX_DDL.add_path('json_pl_excl', 'VARCHAR2', '$.ShippingInstructions.name');
  CTX_DDL.add_path('json_pl_excl', 'VARCHAR2', '$.ShippingInstructions.Address.zipCode');
END;
/

-- declare the pathlist preference name

CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc)
  FOR JSON PARAMETERS ('PATHLIST json_pl_excl');

Related Topics