python - jsonb 连接在 sqlalchemy 中无法正常工作

问题描述

我有一个查询连接到 postgres 中的 jsonb 类型列，我想使用 aldjemy 包将其转换为 django 中的 sqlalchemy

SELECT anon_1.key AS tag, count(anon_1.value ->> 'polarity') AS count_1, anon_1.value ->> 'polarity' AS anon_2
FROM  feedback f
         JOIN tagging t ON t.feedback_id = f.id
         JOIN jsonb_each(t.json_content -> 'entityMap') AS anon_3 ON true
         JOIN jsonb_each(((anon_3.value -> 'data') - 'selectionState') - 'segment') AS anon_1 ON true
where f.id = 2
GROUP BY anon_1.value ->> 'polarity', anon_1.key;

json_content 字段以以下格式存储数据：

{
  "entityMap":
  {
    "0":
    {
      "data":
      {
        "people":
        {
          "labelId": 5,
          "polarity": "positive"
        },
        "segment": "a small segment",
        "selectionState":
        {
          "focusKey": "9xrre",
          "hasFocus": true,
          "anchorKey": "9xrre",
          "isBackward": false,
          "focusOffset": 75,
          "anchorOffset": 3
        }
      },
      "type": "TAG",
      "mutability": "IMMUTABLE"
    },
    "1":
    {
      "data":
      {
        "product":
        {
          "labelId": 6,
          "polarity": "positive"
        },
        "segment": "another segment",
        "selectionState":
        {
          "focusKey": "9xrre",
          "hasFocus": true,
          "anchorKey": "9xrre",
          "isBackward": false,
          "focusOffset": 138,
          "anchorOffset": 79
        }
      },
      "type": "TAG",
      "mutability": "IMMUTABLE"
    }
  }
}

我写了下面的sqlalchemy代码来实现查询

first_alias = aliased(func.jsonb_each(Tagging.sa.json_content["entityMap"]))
print(first_alias)
second_alias = aliased(
    func.jsonb_each(
        first_alias.c.value.op("->")("data")
        .op("-")("selectionState")
        .op("-")("segment")
    )
)

polarity = second_alias.c.value.op("->>")("polarity")

p_tag = second_alias.c.key
_count = (
    Feedback.sa.query()
    .join(
        CampaignQuestion,
        CampaignQuestion.sa.question_id == Feedback.sa.question_id,
        isouter=True,
    )
    .join(Tagging)
    .join(first_alias, true())
    .join(second_alias, true())
    .filter(CampaignQuestion.sa.campaign_id == campaign_id)
    .with_entities(p_tag.label("p_tag"), func.count(polarity), polarity)
    .group_by(polarity, p_tag)
    .all()
)
print(_count)

但它NotImplementedError: Operator 'getitem' is not supported on this expression在访问时给了我一个错误first_alias.c

堆栈跟踪：

Traceback (most recent call last):
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/rest_framework/views.py", line 506, in dispatch
    response = handler(request, *args, **kwargs)
  File "/home/work/api/app/campaign/views.py", line 119, in results_p_tags
    d = campaign_service.get_p_tag_count_for_campaign_results(id)
  File "/home/work/api/app/campaign/services/campaign.py", line 177, in get_p_tag_count_for_campaign_results
    return campaign_selectors.get_p_tag_counts_for_campaign(campaign_id)
  File "/home/work/api/app/campaign/selectors.py", line 196, in get_p_tag_counts_for_campaign
    polarity = second_alias.c.value.op("->>")("polarity")
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 1093, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py", line 746, in columns
    self._populate_column_collection()
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py", line 1617, in _populate_column_collection
    self.element._generate_fromclause_column_proxies(self)
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py", line 703, in _generate_fromclause_column_proxies
    fromclause._columns._populate_separate_keys(
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/base.py", line 1216, in _populate_separate_keys
    self._colset.update(c for k, c in self._collection)
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/base.py", line 1216, in <genexpr>
    self._colset.update(c for k, c in self._collection)
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", line 434, in __getitem__
    return self.operate(getitem, index)
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 831, in operate
    return op(self.comparator, *other, **kwargs)
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", line 434, in __getitem__
    return self.operate(getitem, index)
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", line 75, in operate
    return o[0](self.expr, op, *(other + o[1:]), **kwargs)
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py", line 173, in _getitem_impl
    _unsupported_impl(expr, op, other, **kw)
  File "/home/.cache/pypoetry/virtualenvs/api-FPSaTdE5-py3.8/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py", line 177, in _unsupported_impl
    raise NotImplementedError(
NotImplementedError: Operator 'getitem' is not supported on this expression

任何帮助将不胜感激

PS：我使用的 sqlalchemy 版本是 1.4.6 我之前在使用 sqlalchemy 版本 1.3.22 的烧瓶项目中使用了相同的 sqlalchmy 查询表达式，它工作正常

标签： pythondjangopostgresqlsqlalchemyjsonb

python - jsonb 连接在 sqlalchemy 中无法正常工作

问题描述

解决方案

推荐阅读