Secondary Index on JSON keys [Deprecated]

FB DocStore is deprecated, and users will need to find alternative solutions.

Secondary indexes can be created on document paths, or combination of document paths and regular columns. Because JSON values are loose types, we cannot infer type information from a JSON key (path) - a key may map to different value types in different rows. Therefore, in defining a secondary index on document path, a type needs to be specified.

Syntax

We currently support creating INT and STRING indexes on document paths.

<document_virtual_key> ::= <document_path> AS <document_virtual_key_type>
<document_virtual_key_type> ::= int | string '(' number ')'

-- new table ddl
create table t1 (
  id int not null,
  doc document not null,
  primary key(id),
  key zipcode_idx(doc.address.zipcode as int),
  key street_idx(doc.address.street as string(255))) engine=innodb;

-- Or using alter to add an int index to existing table
alter table t1 add key zipcode_idx(doc.address.zipcode as int);

-- add a string index to existing table
alter table t1 add key street_idx(doc.address.street as string(255));

Type conversion for document path indexes

Since JSON by nature contains mixed data types, even if we specify type hint INT for the document path index, each row may contains different forms of data in key value. We will do the best effort to convert the data into the index type as much as possible, and the conversions should be meaningful to JSON document. For any values of the document path that cannot be converted, a NULL value will be stored for the row.

Enabling/Disabling optimizer for document path indexes

We introduces a special hint to enable all document keys to participate in the query optimization.

-- enable document keys for a table
USE DOCUMENT KEYS|INDEXES
-- disable document keys for a table
IGNORE DOCUMENT KEYS|INDEXES

Below are some examples using the new hint.

-- point query with covering index
select id from t1 use document keys where doc.address.zipcode = 98761;

-- aggregate query with covering index
select count(*) from t1 use document keys group by doc.address.zipcode;