json_path_processing index parameter

Enable the json_path_processing index parameter to include paths as part of the field names in field-value pairs from JSON or BSON documents.

You can enable the json_path_processing index parameter if you enable the indexing of field name-value pairs with either one of the following index parameters:

  • The json_names index parameter: In the list of fields to index, you can specify relative paths or full paths. For example, if the full path is person.parents.surname, you can specify the relative path parents.surname.
  • The all_json_names index parameter: Full paths are indexed for all fields.

If you add the json_array_processing index parameter, the paths include array positions, for example: person.cars.0.

Example: Index all field name-value pairs and paths

The following statement creates a bts index with the all_json_names and json_path_processing index parameters enabled on the example JSON docs column:

CREATE INDEX bts_idx 
          ON json_tab (docs bts_json_ops) 
       USING bts(
                 all_json_names="yes",
                 json_path_processing="yes");

The resulting index contains the following field name-value pairs that include paths:

person.givenname: jim
person.surname: flynn
person.age: 29
person.cars: dodge
person.cars: car
person.parents.givenname: slim
person.parents.surname: flynn
person.parents.givenname: lynn
person.parents.surname: kim

Example: Index specified field name-value pairs and paths

The following statement creates a bts index with the json_names and json_path_processing index parameters on the example JSON docs column:

CREATE INDEX bts_idx 
          ON json_tab (docs bts_json_ops) 
       USING bts(
                 json_names="(parents.surname,
                              person.givenname)",
                 json_path_processing="yes");

The parents.surname path is a relative path instead of the full path, person.parents.surname.

The resulting index contains the following field name-value pairs that include paths:

person.givenname: jim
parents.surname: flynn
parents.surname: kim