OrientDB JSON 数据中的全文搜索 [英] Full Text Search in OrientDB JSON Data

问题描述

我在 OrientDB 3.0.27 中有以下数据，其中一些值在 JSON 数组中，一些是字符串

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"
}

我尝试了以下查询:

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.

我想运行一个包含查询，该查询将在具有任何数据类型(如字符串、数字、JSON 数组等)的任何字段中进行搜索，更像是 - 全文搜索.

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;

上述查询不起作用，因为实际值在 JSON 数组中.尝试了过滤部分文档

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?

编辑#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%'

然而，使用 any().asString() 不会产生任何结果

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.

推荐答案

如果需要搜索所有的列值，那么我们可以创建一个全行数据的 JSON 对象并将其转换为 String.

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.

然后用like关键字查询字符串，如下:

Then query the string with like keyword, as follows:

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

如果我们将直接转换为 @this.asString() ，那么我们将获得数组元素的计数，而不是数组元素内的真实数据，如下所示:

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

因此，我们需要先转成JSON，再转成String，使用@this.toJSON().asString()

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

参考文献:

• 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

这篇关于OrientDB JSON 数据中的全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！