6 SODA Index Specifications (Reference)
You can index the data in JSON documents using index specifications. A detailed definition of SODA index specifications is presented.
Note:
-
To create a B-tree index you need Oracle Database Release 12c (12.2.0.1) or later. To create a B-tree index that indexes a
DATE
or aTIMESTAMP
value you need Oracle Database Release 18c (18.1) or later. -
To create a spatial index or a search index you need Oracle Database Release 12c (12.2.0.1) or later.
An index specification is a JSON object that specifies a particular kind of database index, which is used for operations on JSON documents. You can specify these kinds of index:
-
B-tree: Used to index scalar JSON values. It is identified by the presence of field
fields
. (Only a B-tree index has this field.) -
Spatial: Used to index GeoJSON geographic data. It is identified by the presence of field
spatial
. (Only a spatial index has this field.) -
Search: Used for one or both of the following:
-
Ad hoc structural queries or full-text searches
-
JSON data guide
A search index specification is identified by the lack of fields
fields
andspatial
. -
Each kind of index specification requires a
name
field, which is a string that names the
index.
B-Tree Index Specifications
A SODA B-tree index specification specifies a
B-tree function-based index on SQL/JSON function json_value
, which
is used by SODA to query JSON documents for scalar values. A B-tree index
specification can have the following fields. Field fields
is
required for a B-tree index specification. The other fields are
optional.
-
fields
— Array of objects, each of which targets a field in the indexed documents that has a scalar JSON value. When the array has more than one element the index specification results in the creation of a composite B-tree index.The order of the elements in array
fields
specifies the primary order of indexing, that is, the order among the targeted fields. The field of the first array element has the highest priority; the field of the last element has the lowest priority.Each object in the array can have the following fields:
-
path
— String specifying the path to the targeted field, whose value is expected to be a scalar. Required.If there are any array steps in the path then only the first element of each such array is used for indexing. In your documents, only scalar values for the targeted field are handled by the index — any non-scalar values for the field are ignored by the index.
-
datatype
— String naming the data type of the targeted-field value, for indexing purposes. Optional. Possible values (all are interpreted case-insensitively):"varchar2"
(default),"number"
,"date"
,"timestamp"
, and the"varchar2"
synonyms"string"
and"varchar"
.An index can be used to improve performance when evaluating a QBE filter criterion if the effective type of the input data matched by QBE filter criteria matches the index
datatype
value.For an index to be picked up, to evaluate a given QBE, it is sufficient that the scalar JSON value targeted by the QBE be interpreted as being of the same SQL data type as the value of index-specification field
datatype
. This is the case for a JSON number value or string value and an indexdatatype
of"number"
or"varchar2"
(or a"varchar2"
synonym or nodatatype
), respectively.For other
datatype
values there is no directly corresponding JSON scalar data type, so for a QBE to pick up the index it needs to use an item-method operator, to transform the JSON value to a SQL value of the appropriate data type.For example, in a QBE such as
{"dateField" : {"$date" : "2017-07-25"}}
the input string value"2017-07-25"
(which has one of the supported ISO 8601 date formats) is converted by QBE item-method operator$date
to data type"date"
. An index specified with adatatype
value of"date"
can be picked up to evaluate the QBE.A QBE that does not explicitly use item-method operator
$number
or$string
can pick up an index whosedatatype
is"number"
or"varchar2"
(or one of its synonyms), respectively, because of the direct correspondence between JSON and SQL data types for such values. For example:-
Using QBE
{"numField" : 20}
, like using{"numField" : {"$number" : 20}}
, can pick up an index created withdatatype
value"number"
. -
Using QBE
{"stringField" : "my string"}
, like using{"stringField" : {"$string" : "my string"}}
, can pick up an index created withdatatype
value"varchar2"
(or one of its synonyms).
-
-
maxlength
— Number specifying the maximum length of the value to index. Optional. Ignored if thedatatype
is one (such asnumber
) that has no length. Ifmaxlength
is not specified then the length of the value indexed is 4000 divided by the number of string fields that are indexed. -
order
— Index sorting order. Optional. The value of fieldorder
can be the string"asc"
or the number1
, meaning ascending order, or the string"desc"
or the number-1
, meaning descending order. Default: ascending order.
-
-
unique
— Boolean. Optional. Whether the index is unique. Default: nonunique (false
). -
indexNulls
— Boolean. Optional. Whether to indexNULL
values for the selected columns (by appending the numeric value 1 to the list of columns to index). Default: do not indexNULL
values (false
).Note:
You must specify a
true
value forindexNulls
, for the index to be picked up for the orderby clause of a QBE.
The default error-handling behavior is that the targeted field need
not be present, but if it is present then its value must be a a JSON scalar that is
convertible to data type datatype
. An error is raised at query time
if, for any document, that is not the case. In addition, if such an index exists,
and you try to write a document where that is not the case, then an error is raised
for the write operation.Foot 1
A B-tree
index specification can specify an alternative error-handling behavior from
the default by including field scalarRequired
with a
true
value. That requires that the targeted field be present
and have a value convertible to data type datatype
. If, for any
document to be indexed, that is not the case then an error is raised at indexing
time. In addition, if such an index exists, and you try to write a document where
that is not the case, then raise an error for the write operation.Foot 2
Note:
A JSON null
value in your data is always
convertible to the data type specified for the index. That data is simply not
indexed. (This is true regardless of the value of
scalarRequired
.)
Spatial Index Specifications
A SODA spatial index specification specifies an Oracle Spatial and
Graph index, which indexes GeoJSON data. A spatial index specification has a
spatial
field, whose value is a string specifying
the path to the JSON field to be indexed. The value of that targeted JSON field is
expected to be a single GeoJSON geometry
object, that is, a JSON
scalar that is also a GeoJSON geometry
object.
The default error-handling behavior is that the targeted field need not be present,
but if it is present then its value must be a single GeoJSON
geometry
object. An error is raised at indexing time if, for
any document to be indexed, that is not the case. In addition, if such an index
exists, and you try to write a document where that is not the case, then an error is
raised for the write operation.Foot 3
A spatial
index specification can specify an alternative error-handling behavior from
the default by including one of the following Boolean fields with a
true
value. (Only one of these error-handling fields can
be specified as true
; otherwise, a syntax error is raised at
index-creation
time.)
-
scalarRequired
— Boolean. Optional. The targeted field must be present and have a GeoJSONgeometry
object as its value. Raise an error at indexing time if, for any document to be indexed, that is not the case. In addition, if such an index exists, and you try to write a document where that is not the case, then raise an error for the write operation.Foot 4 -
lax
— Boolean. Optional. The targeted field need not be present or have a GeoJSONgeometry
object as its value. Do not raise an error at indexing time for any document to be indexed that lacks the field or for which the field value is not geometry. In addition, if such an index exists, and you try to write a document where that is the case, do not raise an error for the write operation.Foot 5
Note:
If you have created a SODA spatial index for a field whose value is a GeoJSON geometry
object, and if you use a QBE that targets that field, then the index can be picked up for the QBE only if both index and QBE specify the same error-handling behavior for that field. Both must specify the same one of these:
-
scalarRequired : true
-
lax : true
-
Neither
scalarRequired : true
norlax : true
Search Index Specifications
A SODA search index specification specifies a JSON search index, which indexes the textual context of your JSON documents in a general way. A search index can improve the performance of both (1) ad hoc structural queries, that is, queries that you might not anticipate or use regularly, and (2) queries that make use of full-text search. It is an Oracle Text index that is designed specifically for use with JSON data.
A JSON search index can also accumulate and update aggregate information about your documents. In this it provides a JSON data guide, which is a summary of the structural and type information contained in a set of JSON documents. It records metadata about the fields used in those documents.
You can use data-guide information to:
-
Generate a JSON Schema document that describes the set of JSON documents.
-
Create database views that you can use to perform SQL operations on the data in the documents.
-
Automatically add or update virtual database columns that correspond to added or changed fields in the documents.
The data-guide information contained in a JSON search index is updated automatically as new JSON content is added.
By default, a
search index specification creates an index that provides both of these features: a
general index and a data guide. These features are specified by fields
search_on
(string) and
dataguide
(string), respectively.
If
field search_on
is present with value
"none"
then the index provides only the data-guide
functionality (no general search index). If field dataguide
is
present with value "off"
then only the general search-index
functionality is provided (no data-guide support). (A dataguide
value of "on"
, or no field dataguide
, specifies
data-guide support).
Besides none
, field
search_on
can also have value "text"
or
"text_value"
. Both of these support full-text
queries, which use QBE operator $contains
, and they both support ad
hoc queries that make of other QBE operators, such as $eq
,
$ne
, and $gt
.
In addition,
search_on
value "text_value"
indexes numeric
ranges. This is a separate value because it has an added performance cost. If you do
not need range indexing then you can save some index maintenance time and some disk
space by specifying value text
instead of
text_value
. The default value of search_on
is
text_value
.
See Also:
-
Oracle Database JSON Developer’s Guide for information about using SQL to create
json_value
B-tree indexes -
Oracle Database JSON Developer’s Guide for information about using SQL to index multiple JSON fields with a composite
json_value
B-tree index -
Ordering Columns in an Index in Oracle Database Performance Tuning Guide, and Oracle Database SQL Language Reference, section ASC | DESC, for information about indexing order
-
Oracle Database JSON Developer’s Guide for information about the use of a
NULL ON EMPTY
clause for a B-tree index created on ajson_value
expression -
Oracle Database JSON Developer’s Guide for information about JSON search indexes
-
ISO 8601 for information about the ISO date formats
-
Oracle Spatial Developer's Guide for information about spatial indexes
Footnote Legend
Footnote 1: The default error-handling behavior corresponds to the use of SQL clausesERROR ON ERROR
and
NULL ON EMPTY
for a functional index created on a
json_value
expression.Footnote 2: A
true
value of scalarRequired
corresponds
to the use of SQL clause ERROR ON ERROR
for a functional index
created on a json_value
expression.Footnote 3: The default error-handling behavior corresponds to the use of SQL clauses
ERROR ON ERROR
and
NULL ON EMPTY
for a functional index created on a
json_value
expression.Footnote 4: A
true
value of scalarRequired
corresponds to the use of SQL clause ERROR ON ERROR
for
a functional index created on a json_value
expression.Footnote 5: A
true
value
of lax
corresponds to the use of SQL clause
NULL ON ERROR
for a functional index created on a
json_value
expression.