英文:
Azure Search Service unable to map collection of complex objects
问题
我正在使用Azure Search .NET SDK从Azure SQL数据库中获取数据。
除了其他字段之外，数据库视图返回一个具有以下结构的复杂对象列表：
[
{
"AttributeId": 133,
"Value": "07.02.2023",
"Attribute": [
{
"Id": 133,
"Name": "Date",
"DataTypeId": 4
}
]
},
{
"AttributeId": 141,
"Value": "28.01.2023 06:59",
"Attribute": [
{
"Id": 141,
"Name": "Inspection date",
"DataTypeId": 5
}
]
}
]
我用于映射的模型（在.NET中）如下：
public class Entity {
...
public List<Assignment>? Assignments { get; set; }
}
public class Assignment
{
public int AttributeId { get; set; }
public string Value { get; set; }
public Attribute Attribute { get; set; }
}
public class Attribute
{
public int Id { get; set; }
public string Name { get; set; }
public int DataTypeId { get; set; }
}
所以，即使在索引器运行时拥有相同的结构，我得到以下错误：
JSON数组与元素类型 'Object' 映射到 'Collection(Edm.ComplexType)'。期望的类型为 'Edm.ComplexType'
我猜测索引器将数据库的JSON列表识别为 Object，无法映射到列表（Collection），但我不明白为什么以及如何解决这个问题。
有人遇到过相同的问题吗？
编辑：
Azure SQL数据库没有内置的数据类型可以自然地映射到Azure Cognitive Search中的 Collection(Edm.String) 字段
所以我必须使用 FieldMappingFunction，但根据我发现的信息，我只能使用它们定义的函数。它们中没有一个在我的情况下有用，因为它们有 jsonArrayToStringCollection，但我需要 jsonArrayTo<COMPLEX>Collection。
英文:
I'm using Azure Search .NET SDK to ingest data from Azure SQL Database.
Besides other fields, the database View returns a list of complex objects with the following structure:
[
{
"AttributeId": 133,
"Value": "07.02.2023",
"Attribute": [
{
"Id": 133,
"Name": "Date",
"DataTypeId": 4
}
]
},
{
"AttributeId": 141,
"Value": "28.01.2023 06:59",
"Attribute": [
{
"Id": 141,
"Name": "Inspection date",
"DataTypeId": 5
}
]
}
]
And the model I use for mapping (in .NET):
public class Entity {
...
public List<Assignment>? Assignments { get; set; }
}
public class Assignment
{
public int AttributeId { get; set; }
public string Value { get; set; }
public Attribute Attribute { get; set; }
}
public class Attribute
{
public int Id { get; set; }
public string Name { get; set; }
public int DataTypeId { get; set; }
}
So, even if I have the same structure when the indexer is running the tries to ingest the data, I get the following error:
JSON arrays with element type 'Object' map to Collection(Edm.ComplexType)'). The expected type was 'Edm.ComplexType'
I presume that the indexer recognizes the database JSON list as an Object and cannot be mapped to List (Collection), but I can't understand why and how I can fix this problem.
Has anyone faced the same problem?
Edited:
Azure SQL Database doesn't have a built-in data type that naturally maps to Collection(Edm.String) fields in Azure Cognitive Search
So I have to use a FieldMappingFunction but from what I found, I can use only their defined functions. None of them are useful in my case because they have jsonArrayToStringCollection but I need jsonArrayTo<COMPLEX>Collection
答案1
得分: 0
根据官方示例，你应该能够对Assignments进行索引。我认为问题出在List<T>上。尝试将其改为数组：
从：
public List<Assignment>? Assignments { get; set; }
改为：
public Assignment[] Assignments { get; set; }
英文:
According to official samples, you should be able to index Assignments. I do think the issue is around List<T>. Try to make it an array instead:
from:
public List<Assignment>? Assignments { get; set; }
to:
public Assignment[] Assignments { get; set; }
答案2
得分: 0
我已成功解决了这个问题。默认情况下（即使您使用SELECT TOP 1），FOR JSON AUTO/PATH 子句会用方括号包围结果。所以，在我的情况下，我期望得到的是 "Attribute": {"Id": 141, "Name": "Inspection date", "DataTypeId": 5 }，就像我在 Attribute 类中已经有的那样。
英文:
I've managed to fix the problem. By default (even if you are using SELECT TOP 1), the FOR JSON AUTO/PATH clause surrounds the result with square brackets. So, in my case instead of "Attribute": [{"Id": 141, "Name": "Inspection date", "DataTypeId": 5 }] I expected to have "Attribute": {"Id": 141, "Name": "Inspection date", "DataTypeId": 5 } as I already had in the Attribute class.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论