Feature: Indexing Lists

[gramian]

Currently, as far as I understand, list properties are indexed as a whole not element-wise. On the other hand, objects can be index by their contents (using BY KEY or BY VALUE), see https://docs.arcadedb.com/#SQL-Create-Index .
So I would like to propose to enhance indexing a list by item (for example noted by BY ITEM in the CREATE INDEX command).

This feature seems to have been scoped for OrientDB, too, see:

• index on embeddedlist (array of objects) orientechnologies/orientdb#4976
• Support indexing embedded types Lists/Objects orientechnologies/orientdb#5201
• Support for indexes on fields of embedded properties type like EMBEDDED/EMBEDDEDLIST/EMBEDDEDMAP/EMBEDDEDSET orientechnologies/orientdb#7093

and is also a feature in other NoSQL DBMSs:

• CouchBase: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html
• MongoDB: https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-basic/

[lvca]

First quick analysis:

1. compute the delta between the list before and after the change
2. for each element removed, remove the entry in the index
3. for each element added, add the entry in the index
4. that's it, it looks quite simple to me at first glance

[gramian]

My practical application is:

Given the schema (excerpt):

CREATE DOCUMENT TYPE pair;

CREATE PROPERTY pair.name STRING (max 255);
CREATE PROPERTY pair.data STRING (max 4095);

CREATE VERTEX TYPE metadata;

CREATE PROPERTY metadata.identifiers LIST OF pair (max 255);

I want to index the data property of the identifiers list of pairs.

[DMCTowns]

Plus one for this feature - it would really help me out

I'm building a system that will import data from multiple sources where there's a high chance of duplicates. I have a Vertex type called DataCollectionEntity that has an array of 'Identifier' Documents as one of its properties. When importing a new entity, I'm doing a match against the identifiers in the existing data and working out a probability of the new document being a duplicate.

An example document might look like:

{
  id: 'ABC',
  identifiers: [
    {
      name: "CompanyName",
      type: "STRING",
      value: "Apple Inc",
      unique: false
    },
    {
      name: "ISIN",
      type: "STRING",
      value: "123456789",
      unique: true
    },
    {
      name: "Symbol",
      type: "STRING",
      value: "AAPL",
      unique: true
    }
  ]
}

I might then query the dataset using a subset of the identifiers such as:

SELECT DISTINCT * FROM DataCollectionEntity 
   WHERE (identifiers CONTAINS (name='CompanyName' AND type='STRING' AND value='Apple Inc') 
        OR identifiers CONTAINS (name='ISIN' AND type='STRING' AND value='123456789'))

So indexes on identifiers[n].name, identifiers[n].type and identifiers[n].value should make this a lot more performant

[DMCTowns]

@lvca do you have any thoughts on if/when this might be implemented?

[gramian]

This is awesome. I will test this next week and add to the docs. Thank you!

[lvca]

@DMCTowns This is finally in our codebase. You can already use it in development ArcadeDB v25.10.1-SNAPSHOT) and it will be official release in the v25.10.1 at the end of the month or earlier).