Full Text Search in OrientDB JSON Data

Question

I have following data in OrientDB 3.0.27 where some of the values are in JSON Array and some are string

{
    "@type": "d",
    "@rid": "#57:0",
    "@version": 2,
    "@class": "abc_class",
    "user_name": [
        "7/1 LIBOR Product"
    ],
    "user_Accountability": [],
    "user_Rollout_32_date": [],
    "user_Brands": [
        "AppNet"
    ],
    "user_lastModificationTime": [
        "2019-11-27 06:40:35"
    ],
    "user_columnPercentage": [
        "0.00"
    ],
    "user_systemId": [
        "06114a87-a099-0c30c60b49c4"
    ],
    "user_lastModificationUser": [
        "system"
    ],
    "system_type": "Product",
    "user_createDate": [
        "2017-10-27 09:58:42"
    ],
    "system_modelId": "bian_model",
    "user_parent": [
        "a12a41bd-af6f-0ca028af480d"
    ],
    "user_Strategic_32_value": [],
    "system_oeId": "06114a87-a099-0c30c60b49c4",
    "user_description": [],
    "@fieldTypes": "user_name=e,user_Accountability=e,user_Rollout_32_date=e,user_Brands=e,user_lastModificationTime=e,user_columnPercentage=e,user_systemId=e,user_lastModificationUser=e,user_createDate=e,user_parent=e,user_Strategic_32_value=e,user_description=e"
}

I have tried following queries:

select * from `abc_class ` where any() = ["AppNet"]  limit 2;
select * from `abc_class ` where any() like '%a099%'  limit 2;

Both of the above queries work since they are respecting the datatype of the field.

I want to run a contains query which will search in ANY field with ANY data type (like String, number, JSON Array, etc) more of like a - full text search.

select * from `abc_class ` where any() like '%AppNet%'  limit 2;

The above query doesn't work since the real value is inside JSON Array. Tried almost all the things from filtering section documentation

How can I achieve full-text search like functionality with the existing data?

EDIT # 1

After doing more research now I'm able to atleast convert the array value into string and then run like operator on it, like below;

select * from `abc_class` where user_name.asString() like '%LIBOR%'

However, using any().asString() doesn't result any result

select * from `abc_class` where any().asString() like '%LIBOR%'

If the above query can be enhanced somehow to query any column as string, then the problem can be resolved.

Answer 1

If all the column values needs to be searched then we can create a JSON object of the full row data and convert it into String.

Then query the string with like keyword, as follows:

select * from `abc_class` where @this.toJSON().asString() like '%LIBOR%'

If we will be converting to @this.asString() directly then we'll be getting the count of array elements instead of the real data inside the array elements like below:

abc_class#57:4{system_modelId:model,system_oeId:14f4b593-a57d-4d37ad070a10,system_type:Product,user_lastModificationUser:[1],user_name:[1],user_description:[0],user_Accountability:[0],user_lastModificationTime:[1],user_Rollout_32_date:[0],user_Strategic_32_value:[0],user_createDate:[1],user_Brands:[0],user_parent:[1],user_systemId:[1],user_columnCompletenessPercentage:[1]} v2

Therefore, we need to first convert into JSON and then into String to query the full record using @this.toJSON().asString()

References:

• https://orientdb.com/docs/last/sql/SQL-Methods.html
• https://orientdb.com/docs/last/sql/SQL-Where.html
• https://orientdb.com/docs/last/sql/SQL-Syntax.html