json_array_processing index parameter

Enable the json_array_processing index parameter to index the array positions of values in JSON or BSON documents as field names.

Array positions are numbers, starting with 0, which represent the position of the value in the array. For example, the array "cars" : [ "dodge", "olds" ] has two positions:

"cars".
        "0" : "dodge"
        "1" : "olds"

In this example, the field name for dodge is 0 and the field name for olds is 1. Field names that are only numbers cannot be queried, and are therefore not indexed. If you index field name-value pairs and array positions, but not paths, then field name-value pairs in arrays are not indexed, because the field names are numbers.

Indexing the array positions is most useful when you also index field name-value pairs and paths. Array positions in a field name that includes a path are indexed because the field name contains more than just a number. For example, the field names with paths from the example array are cars.0 and cars.1.

When array and path processing are both enabled, the paths specified in the json_names index parameter must include array positions.

Example: Index array positions

The following statement creates a bts index with the json_array_processing index parameter enabled on the example JSON docs column:

CREATE INDEX bts_idx 
          ON json_tab (docs bts_json_ops) 
       USING bts(
                 json_array_processing="yes");

The resulting index indexes the following unstructured text that contains the values and the array positions in the document in the contents field:

contents: person givenname jim surname flynn age 29 cars 0 dodge 1 olds 
parents 0 givenname slim surname flynn 1 givenname lynn surname kim

In this example, indexing the array positions does not provide meaningful index entries because the position numbers are not differentiated from other values. If you query for the number 1, you do not know if the number is a value or an array position. Array positions are meaningful only in the context of field names and paths.

Example: Index all field name-value pairs, paths, and array positions

The following statement creates a bts index with the all_json_names, json_path_processing, and json_array_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",
                 json_array_processing="yes");

The resulting index contains the following field name-value pairs that contain paths and array positions:

person.givenname: jim
person.surname: flynn
person.age: 29
person.cars.0: dodge
person.cars.1: olds
person.parents.0.givenname: slim
person.parents.0.surname: flynn
person.parents.1.givenname: lynn
person.parents.1.surname: kim

Example: Index specified field name-value pairs, paths, and array positions

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

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

The array position is required. If you specify parents.surname instead of parents.1.surname, this example results in an error.

The resulting index contains the following field name-value pairs that contain paths and array positions:

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

Example: Index all field name-value pairs and array positions

The following statement creates a bts index with the all_json_names and json_array_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_array_processing="yes");

The resulting index contains the following field name-value pairs and array positions:

givenname: jim
givenname: slim 
givenname: lynn
age: 29
surname: flynn
surname: flynn
surname: kim

The following field name-value pairs are not indexed because the field names are numbers:

0: dodge
1: olds