You can index JSON data as you would any data of the type you use to store it. In addition, you can define a JSON search index, which is useful for both ad hoc structural queries and full-text queries. Show
Topics:
24.1 Overview of Indexing JSON DataThere is no dedicated SQL data type for JSON data, so you can index it in the usual ways. In addition, you can index it in a general way, with a JSON search index, for ad hoc structural queries and full-text queries. You can index JSON data as you would any data of the type that you use to store it. In particular, you can use a B-tree index or a bitmap
index for SQL/JSON function (More generally, a bitmap index can be appropriate wherever the number of possible values for the function is small. For example, you can use a bitmap index for function As always, such function-based indexing is appropriate for queries that target particular functions, which in the context of SQL/JSON functions means particular SQL/JSON path expressions. It is not very helpful for queries that are ad hoc, that is, arbitrary. Define a function-based index if you know that you will often query a particular path expression. If you query in an ad hoc manner then define a JSON search index. This is a general index, not targeted to any specific path expression. It is appropriate for
structural queries, such as looking for a JSON field with a particular value, and for full-text queries using SQL/JSON condition You can of course define both function-based indexes and a JSON search index for the same JSON column. A JSON search index is an Oracle Text (full-text) index designed specifically for use with JSON data. Note: Oracle recommends that you use AL32UTF8 as the database character set. Automatic character-set conversion can take place when creating or applying an index. Such conversion can be lossy, which can mean that some data that you might expect to be returned by a query is not returned. See Character Sets and Character Encoding for JSON Data. 24.2 How To Tell Whether a Function-Based Index for JSON Data Is Picked UpTo determine whether a given query picks up a given function-based index, look for the index name in the execution plan for the query. For example, given the index defined in
Example 24-4, an execution plan for the 24.3 Creating Bitmap Indexes for SQL/JSON Condition JSON_EXISTSYou can create a bitmap index for the value returned by This is illustrated by Example 24-1. Example 24-2 creates a bitmap index for a value returned by Example 24-1 Creating a Bitmap Index for JSON_EXISTS
Example 24-2 Creating a Bitmap Index for JSON_VALUE
24.4 Creating JSON_VALUE Function-Based IndexesYou can create a function-based index for SQL/JSON function
Example 24-4 creates a function-based index for The use of An alternative is to create an index using the simplified syntax described in Simple Dot-Notation Access to JSON Data. Example 24-3 illustrates this; it indexes both scalar and non-scalar results, corresponding to what a dot-notation query can return. The indexes created in both Example 24-4 and
Example 24-3 can be picked up for either a query that uses dot-notation syntax or a query that uses If the index of Example 24-3 is picked up for a If you want to allow indexing of data that might be missing the field targeted by a Oracle recommends that you create a function-based index for
Indexes created in either of these ways can thus be used with both dot-notation queries and Example 24-3 Creating a Function-Based Index for a JSON Field: Dot Notation
Example 24-4 Creating a Function-Based Index for a JSON Field: JSON_VALUE
Example 24-5 Specifying NULL ON EMPTY for a JSON_VALUE Function-Based Index Because of clause
24.5 Using a JSON_VALUE Function-Based Index with JSON_TABLE QueriesAn index created using The index acts as a constraint on the indexed path, to ensure that only one (non- The query in Example 24-6 thus makes use of the index created in Example 24-4. Note: A function-based index created using a See Oracle Database SQL Language Reference for information about SQL comparison conditions. Example 24-6 Use of a JSON_VALUE Function-Based Index with a JSON_TABLE Query
24.6 Using a JSON_VALUE Function-Based Index with JSON_EXISTS QueriesAn index created using SQL/JSON function In order for a For example, if the index returns a number then the
comparison type must also be number. If the query filter expression contains more than one comparison that matches a The type of a comparison is determined as follows:
Example 24-4 creates a a function-based index for Each of the queries
Example 24-7, Example 24-8, and
Example 24-9 can make use of this index when evaluating its Example 24-7 JSON_EXISTS Query Targeting Field Compared to Literal Number This query makes use of the index because:
Example 24-8 JSON_EXISTS Query Targeting Field Compared to Variable Value This query can make use of the index because:
Example 24-9 JSON_EXISTS Query Targeting Field Cast to Number Compared to Variable Value This query can make use of the index because:
Example 24-10 JSON_EXISTS Query Targeting a Conjunction of Field Comparisons Just as for Example 24-7, this query can make use of the index on field
24.7 Data Type Considerations for JSON_VALUE Indexing and QueryingBy default, SQL/JSON function For example, in the query of Example 24-11, But the index created in Example 24-3 does not use Now consider the queries in
Example 24-12 and Example 24-13, which use In
Example 24-12, SQL function Neither of the indexes of Example 24-4 and Example 24-3 is picked up for either of these queries. The queries might return the right results in each case, because of type-casting, but the indexes cannot be used to evaluate the queries. Consider also what happens if some of the data cannot be converted to a particular data type. For example, given the queries in Example 24-11,
Example 24-12, and Example 24-13, what happens to a For
Example 24-12 and Example 24-13, the query stops in error because of the attempt to cast the value to a number. For
Example 24-11, however, because the default error handling behavior is Similarly, if the query used, say, Example 24-11 JSON_VALUE Query with Explicit RETURNING NUMBER
Example 24-12 JSON_VALUE Query with Explicit Numerical Conversion
Example 24-13 JSON_VALUE Query with Implicit Numerical Conversion
24.8 Indexing Multiple JSON Fields Using a Composite B-Tree IndexTo index multiple fields of a JSON object, you first create virtual columns for them. Then you create a composite B-tree index on the virtual columns. Example 24-14 and
Example 24-15 illustrate this. Example 24-14 creates virtual columns Example 24-15 creates a composite B-tree index on the virtual columns of Example 24-14. A SQL query that references either the virtual columns or the corresponding JSON data (object fields) picks up the composite index. This is the case for both of the queries in Example 24-16. These two queries have the same effect, including the same performance. However, the first query form does not target the JSON data itself; it targets the virtual columns that are used to index that data. The data does not depend logically on any indexes implemented to improve query performance. If you want this independence from implementation to be reflected in your code, then use the second query form. Doing that ensures that the query behaves the same functionally with or without the index — the index serves only to improve performance. Example 24-14 Creating Virtual Columns For JSON Object Fields
Example 24-15 Creating a Composite B-tree Index For JSON Object Fields
Example 24-16 Two Ways to Query JSON Data Indexed With a Composite Index
24.9 JSON Search Index: Ad Hoc Queries and Full-Text SearchA JSON search index is a general index. It can improve the performance of both (1) ad hoc structural queries, that is, queries that you might not anticipate or use regularly, and (2) full-text search. It is an Oracle Text index that is designed specifically for use with JSON data. Note: If you created a JSON search index using Oracle Database 12c Release 1 (12.1.0.2) then Oracle
recommends that you drop that index and create a new search index for use with later releases, using Introduction to JSON Search Indexes You create a JSON search index using If the name of your JSON search index is present in the execution plan for your query, then you know that the index was in fact picked up for that query. You will see a line similar to that shown in Example 24-18. You can specify a A JSON search index is maintained asynchronously, on demand. You can thus defer the cost of index maintenance, performing it at commit time only or at some time when database load is reduced. This can improve DML performance. It can also improve index maintenance performance by enabling bulk loading of unsynchronized index rows when an index is synchronized. On the other hand, asynchronous maintenance of an index means that until it is synchronized the index is not used for data that has been modified or newly inserted. If your queries that make use of a JSON search index never involve
numeric ranges then you can save some index maintenance time and some disk space by specifying Note: To alter a JSON search index Example 24-17 Creating a JSON Search Index
Example 24-18 Execution Plan Indication that a JSON Search Index Is Used
Full-Text Search of JSON Data You can use SQL/JSON condition To be able to use condition Example 24-19 shows a full-text query that finds purchase-order documents that contain the keyword Example 24-19 Full-Text Query of JSON Data
Example 24-20 Full-Text Query of JSON Data, with Escaped Search Pattern If the third argument to condition The queries here search for documents where the part description contains both The first query escapes the single character
Ad Hoc Queries of JSON Data Example 24-21 shows some non full-text queries of JSON data that also make use of the JSON search index created in Example 24-17. Example 24-21 Some Ad Hoc JSON Queries This query selects documents that contain a shipping instructions address that includes a country.
This query selects documents that contain user
This query selects documents where the user is
See Also:
How do I query a JSON column in MySQL?MySQL provides two operators ( -> and ->> ) to extract data from JSON columns. ->> will get the string value while -> will fetch value without quotes. As you can see ->> returns output as quoted strings, while -> returns values as they are. You can also use these operators in WHERE clause as shown below.
Can JSON be indexed?You can index JSON data as you would any data of the type that you use to store it. In particular, you can use a B-tree index or a bitmap index for SQL/JSON function json_value , and you can use a bitmap index for SQL/JSON conditions is json , is not json , and json_exists .
How do I index a column in MySQL?To create indexes, use the CREATE INDEX command: CREATE INDEX index_name ON table_name (column_name); You can an index on multiple columns.
Does MySQL support JSON columns?MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column: Automatic validation of JSON documents stored in JSON columns.
|