用Django中jsonb字段过滤的子查询计数进行注释

Question

我有一个带有jsonb字段的单一模型。这个jsonb字段中有一个值，可以在其他行之间共享。在通过这个jsonb字段进行筛选时，我正在尝试获取子查询的计数。

一些伪代码，我一直在尝试借用这篇文章中的例子。

alpha_qs = MyModel.objects.filter(class_id="ALPHA")
# unnest jsonb field so I can leverage it via OuterRef
alpha_qs = alpha_qs.annotate(nested_value_id=KeyTextTransform("nested_value_id", "a_jsonb_field"))
related_beta_subquery = MyModel.objects.filter(class_id="BETA", a_jsonb_field__nested_value_id=OuterRef("nested_value_id"))
related_beta_subquery_count = related_beta_subquery.annotate(count=Count("*")).values("count")
alpha_qs = alpha_qs.annotate(related_beta_count=Subquery(related_beta_subquery))

使用此示例数据，我希望顶部实例的related_beta_count为2，因为存在两个具有相同nested_value_id的关联贝塔。

{
"class_id": "ALPHA",
"a_jsonb_field": {
"nested_value_id": 'abc'
}
}
{
"class_id": "BETA",
"a_jsonb_field": {
"nested_value_id": 'abc'
}
}
{
"class_id": "BETA",
"a_jsonb_field": {
"nested_value_id": 'abc'
}
}
{
"class_id": "BETA",
"a_jsonb_field": {
"nested_value_id": 'zyz'
}
}

我在下面收到一个错误，但无法解决。

ProgrammingError: operator does not exist: jsonb = text
LINE 1: ...d AND (U0."a_jsonb_field" -> 'nested_value_id') = ("my_model...
^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Answer 1

我让它在我的用例中工作。我不认为自己精通Django，所以可能有一种更干净的方法来实现这一点。

# Get initial queryset
alpha_qs = MyModel.objects.filter(class_id="ALPHA")
# Pull nested jsonb value out via annotation/KeyTextTransform
alpha_qs = alpha_qs.annotate(nested_value_id=KeyTextTransform("nested_value_id", "a_jsonb_field"))
related_beta_subquery = (
MyModel.objects.filter(
class_id="BETA"
)
# Pull out nested jsonb for subquery as well
.annotate(
nested_value_id=KeyTextTransform("nested_value_id", "a_jsonb_field")
)
# Filter to ensure these BETA models have a matched nested_value_id to my ALPHA models
.filter(
nested_value_id=OuterRef("nested_value_id")
)
# This was honestly the harder part that took a lot of trial and error
.order_by()
.values("nested_value_id")
.annotate(count=Count("*"))
.values("count")
)
alpha_qs = alpha_qs.annotate(
count=Case(
# This Case statement is mostly just bulletproofing. May be unnecessary for your use case
When(a_jsonb_field__has_key="nested_value_id", then=Subquery(related_beta_subquery)),
default=Value(0),
output_field=IntegerField(),
)
)