json_names index parameter

Enable the indexing of specific field name-value pairs in JSON or BSON documents with the json_names index parameter.

The input for the field names for the json_names index parameter can be a comma-separated list of names, an external file, or a table column.

The json_names index parameter

json_names=" { ( field ) | file:directory/filename | table:table.column } "

Table 1. Elements for the json_names index parameter
Element	Description
`column`	The column that contains the field names to index. Separate field names by commas, white spaces, or new-line characters.
`directory`	The location of the file that contains field names to index.
`field`	The field name to index.
`filename`	The name of the file that contains field names to index. Must be readable by the user who creates the index. Separate field names by commas, white spaces, or new-line characters.
`table`	The name of the table with the column that contains the field names to index. Must be readable by the user who creates the index.

The field names that you specify are indexed as fields in the bts index. The values in the fields can be searched. When you query on the JSON or BSON column, you must specify the field name to search in the bts_contains() search predicate. In searches, the default field is the first tag or path in the field list. The bts index does not check whether the fields exist in the column, which means that you can specify fields that you will add to the column after you create the index.

If you enable the json_path_processing index parameter, the field name can include relative or full paths. If you enable the json_array_processing index parameter, the field name can include array positions.

If you want to add new field names to the index, you must drop the index, update the field name list, and then re-create the index.

To view the fields that you indexed, run the bts_index_fields() function.

Example: Index one field name-value pair

The following statement creates a bts index with the json_names index parameter set to a single field name on the example JSON docs column:

CREATE INDEX bts_idx 
          ON json_tab (docs bts_json_ops) 
       USING bts(json_names="surname");

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

surname: flynn
surname: flynn
surname: kim

You must specify the surname field in the search predicate.

Example: Index field name-value pairs from a file

The following statement creates a bts index with the json_names index parameter set to a file that is named jsonfield.txt:

CREATE INDEX bts_idx 
          ON json_tab (docs bts_json_ops) 
       USING bts(json_names="file:/jsonfield.txt");

Example: Index field name-value pairs from a column

The following statement creates a bts index with the json_names index parameter set to a column that is named jsonnames in the json_ref table:

CREATE INDEX bts_idx 
          ON json_tab (docs bts_json_ops) 
       USING bts(json_names="table:json_ref.jsonnames");