MySQL Document Store - Invalid value for argument when creating index

Question

The \help createIndex and Official Docs are different.

I tried following the "shell manual" as it got me the farthest but I am still getting:

db.col.createIndex("name", {"fields": [{"field": "name", "type": "TEXT"}, {"field": "name2", "type": "TEXT"}]})
Invalid value for argument 'fields[0].field' (MySQL Error 5017)

My collection has both name and name2, and I believe this has nothing to do with it.

db.col.find()
[
    {
        "_id": "00005bcdb19f0000000000000001",
        "a": [
            1,
            2
        ],
        "name": "chen",
        "name2": "chen"
    }
]
1 document in set (0.0034 sec)

Any help would be appreciated

Answer 1

It turns out that "field" in "fields" requires a JSONPath. Also, TEXT will require some length.

The official documentation on this is severely lacking and even has some glaring errors. I only figured this out in the course of trying to do the NoSQL equivalent of ALTER TABLE ADD UNIQUE INDEX some_index (field_one, field_two); which seems to be unsupported.

Anyways, try this:

db.col.createIndex(
    "name", {
        "fields": [
            {"field": "$.name", "type": "TEXT(64)"},
            {"field": "$.name2", "type": "TEXT(64)"}
        ]
    }
)

EDIT regarding unique indexes: If you add a regular index to the collection and then go into workbench and drop the index you just created and re-add it as a unique index, that appears to work. But that's just for a single field.

EDIT regarding compund unique indexes: If you create a generated column that concatenates the two fields you want to be unique together and then apply a unique index on it, that will work.