如何用SQLAlchemy在PostgreSQL中存储和访问JSON数组数据？

Question

上下文：

我正在使用Python Flask框架构建一个应用程序原型，该框架旨在将地层（地质）信息存储在postgresqljson列中。在

每一行表示数据来自的位置和其他一些元数据。基本地层信息可以包含任意数量的属性，而不希望以任何方式限制属性集。它们中的每一个都与一个地层单元相连，以其深度的上限和下限来描述。在

方法：

在JSON中，地层记录可以如下所示：（注意，单位可以在深度上重叠）

{
  "strat_units": [
    {
      "id": 1,
      "top": 0,
      "bottom": 20,
      "attribute": "parameter 1",
      "value": 0.1
    },
    {
      "id": 2,
      "top": 10,
      "bottom": 30,
      "attribute": "parameter 2",
      "value": 0.2
    }
  ]
}

从这个例子中可以明显看出，数据的结构使得必须以非关系方式存储数据。但由于该应用程序还将处理地理分析，因此使用支持PostGIS的PostgreSQL似乎更为有利。在

为了在Postgres中存储所需的数据，我使用以下SQLAlchemy模型：

^{pr2}$

问题：

鉴于上述数据结构，如何使用纯SQL和SQLAlchemy（ORM）查询、访问和操作存储在JSON列中的数据？在

例如：

• 我怎样才能SELECT所有具有"parameter x" > y层单元的行
• 如何使用SQLAlchemy以行ID和strat\u单元ID为目标，使用SQLAlchemy操作数据？在

更新：

数据模型已经改变了，但问题依然存在。模型现在如下所示：

班级记录（基本）：

    """represents single stratigraphic units"""

    __tablename__ = 'records'
    __table_args__ = {"schema":"public"}

    # ID of corresponding site:
    site_id         = db.Column(db.Integer, db.ForeignKey('public.sites.id'))

    # depth values:
    depth           = db.Column(db.Numeric, nullable=True)
    upper_boundary  = db.Column(db.Numeric, nullable=True)
    lower_boundary  = db.Column(db.Numeric, nullable=True)

    # stratigraphic properties, represented as key/value store
    properties      = db.Column(JSONB)

JSON的属性如下：

{"key": "value", "loss_on_ignition": "0.1"}

我怎么能

• 获取所有行中的所有唯一键？在
• 获取包含“key”的所有行？在
• 获取“key”=“value”的所有行？在
• 更新行/键？在