当前位置：文江博客话题详情

来自Array存储的JSONB列中的Postgres中的parse本机JSON值，带有实体框架.NET CORE NPGSQL

发布于 2025-01-27 19:20:09 字数 1719 浏览 4 评论 0 原文

我的团队在Postgres中有一张桌子，我们称之为事情。看起来像这样：

thing 表：

ID	值	类型
Integer	JSONB	字符串

这里是一些示例记录

ID	值	字符串
1个	Hello”	[
2	123	编号
3	{“ name”：“ foo”}	对象
4	“ 1，5，10，15]	阵列
5	true	boolean
6	[2，3，7]	阵列

thing 类，

public class Thing
{
    public int Id { get; set; }
    public JsonElement Value { get; set; }
    public string Type { get; set; }
}

我们希望构建一个EF查询，该查询必须将必须必须转移到SQL并在Postgres中调用。

这是我们希望有工作的查询：

var results = context.Things.Where(thing => thing.type == "array" && thing.Value.EnumerateArray().Any(v => v.GetInt32() == 5)));

此查询的结果将返回记录＃4。

不幸的是，该语句不能被转移并给出以下错误：

（iEnumerable＆lt; jsonelement＆gt;）p1.value.enumeratearray（）.any（v =＆gt; v.getInt32（）== 5））'不被翻译。其他信息：方法“ System.Text.json.jsonelement.enumeratearray”的翻译失败。

有人会认为 enumeratearray（）将正确使用以使用Postgres JSON_ARRAY_ELEMENTS（ JSON）功能，但没有运气。

如前所述，必须在数据库上调用此查询（客户端过滤不是选项）。

我们知道 ef.functions.jsonxxx 方法，但是，似乎没有一个能够与本机元素的JSON数组一起使用（至少看来，该数组需要包含具有的对象特性）。

也就是说，是否有人必须实施与此类似的事情，而不必编写原始SQL并愿意分享？

我们真的希望避免必须编写和维护原始SQL。

谢谢！

原文

My team has a table in postgres, let's call it Thing. It looks like this:

Thing Table:

id	value	type
integer	jsonb	string

Here are some sample records

id	value	type
1	"hello"	string
2	123	number
3	{ "name": "foo" }	object
4	[1, 5, 10, 15]	array
5	true	boolean
6	[2, 3, 7]	array

Thing class

public class Thing
{
    public int Id { get; set; }
    public JsonElement Value { get; set; }
    public string Type { get; set; }
}

We were hoping to construct an EF query that must be transpiled into SQL and invoked in Postgres.

Here is the query we were hoping to have work:

var results = context.Things.Where(thing => thing.type == "array" && thing.Value.EnumerateArray().Any(v => v.GetInt32() == 5)));

The result of this query would return record #4.

Unfortunately, the statement can't be transpiled and gives the following error:

(IEnumerable<JsonElement>)p1.Value.EnumerateArray() .Any(v => v.GetInt32() == 5))' could not be translated. Additional information: Translation of method 'System.Text.Json.JsonElement.EnumerateArray' failed.

One would think that EnumerateArray() would be transpiled properly to use the Postgres json_array_elements(json) function, but no such luck.

As previously stated, this query must be invoked on the database (client side filtering is not an option).

We are aware of the EF.Functions.JsonXXX methods, however, none seem to be able to work with json arrays of native elements (it seems that at the very least, the array needs to contain objects with properties).

That said, has anyone had to implement something similar to this without having to write raw sql and be willing to share?

We are really hoping to avoid having to write and maintain raw sql.

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

天荒地未老 2025-02-03 19:20:09

似乎您正在尝试查询其JSON数组值包含某些内容的行。与其使用JSON_ARRAY_ELEMENTS，然后尝试撰写此内容，不如直接查询JSON文档是否包含另一个JSON文档：

SELECT * FROM things WHERE value @> '5';

@＆gt; json可以通过 ef.functions.jsoncontains ，。

还请注意， jsonb_typeof （在ef linq ef.functions.jsontypeof ）可用于查询JSON文档的类型（array，string ...），所以您可能不需要单独的“类型”列。如果您需要索引它，则可以将其作为 json_typeof 的计算列。

It seems like you're trying to query out rows whose JSON array value contains something. Rather than using json_array_elements and then trying to compose over that, you should be able to query directly whether a JSON document contains another JSON document:

SELECT * FROM things WHERE value @> '5';

The @> JSON can be expressed operator in EF LINQ queries via EF.Functions.JsonContains, as per the docs.

Note also that jsonb_typeof (in EF LINQ EF.Functions.JsonTypeof) can be used to query for the type of the json document (array, string...), so you may not need a separate "type" column. If you need that in order to index over it, you can make it a computed column with json_typeof.