如何在PostgreSQL中为JSONB列创建无序比较唯一约束 含SQLAlchemy方案需求

实现思路是通过不可变函数递归排序JSONB内部的所有数组元素和对象键，将结构相同仅顺序不同的JSONB转换为完全一致的结果，再对该函数返回值建立唯一索引即可满足需求。
注：网络上提到的排除约束方案需要额外自定义相等操作符，实现复杂度更高，更推荐下述自定义函数加唯一索引的方案

• 第一步：创建递归排序JSONB的不可变函数

CREATE OR REPLACE FUNCTION jsonb_sort(jsonb) RETURNS jsonb AS $$
BEGIN
  -- 处理数组：递归处理每个元素后按文本值排序
  IF jsonb_typeof($1) = 'array' THEN
    RETURN (
      SELECT jsonb_agg(jsonb_sort(elem) ORDER BY elem::text)
      FROM jsonb_array_elements($1) AS arr(elem)
    );
  -- 处理对象：递归处理每个值后按键名排序
  ELSIF jsonb_typeof($1) = 'object' THEN
    RETURN (
      SELECT jsonb_object_agg(key, jsonb_sort(value) ORDER BY key)
      FROM jsonb_each($1) AS obj(key, value)
    );
  -- 基础类型直接返回
  ELSE
    RETURN $1;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

• 第二步：为目标表创建唯一索引
  将下面SQL中的your_table替换为实际表名，your_jsonb_col替换为实际JSONB列名即可：

CREATE UNIQUE INDEX idx_unique_jsonb_ignore_order 
ON your_table (jsonb_sort(your_jsonb_col));

你可以直接复用上面PostgreSQL层的自定义函数，在SQLAlchemy中按如下方式配置：

• 模型定义示例

from sqlalchemy import Column, Integer, Index, func
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class YourModel(Base):
    __tablename__ = "your_table"
    id = Column(Integer, primary_key=True)
    # 你的JSONB字段
    data = Column(JSONB, nullable=False)

    __table_args__ = (
        # 关联基于自定义排序函数的唯一索引
        Index(
            "idx_unique_jsonb_ignore_order",
            func.jsonb_sort(data),
            unique=True
        ),
    )

• Alembic迁移配置
  在迁移文件中添加函数和索引的创建/销毁逻辑：

import sqlalchemy as sa
from alembic import op

def upgrade():
    # 创建JSONB排序函数
    op.execute("""
    CREATE OR REPLACE FUNCTION jsonb_sort(jsonb) RETURNS jsonb AS $$
    BEGIN
      IF jsonb_typeof($1) = 'array' THEN
        RETURN (SELECT jsonb_agg(jsonb_sort(elem) ORDER BY elem::text) FROM jsonb_array_elements($1) AS arr(elem));
      ELSIF jsonb_typeof($1) = 'object' THEN
        RETURN (SELECT jsonb_object_agg(key, jsonb_sort(value) ORDER BY key) FROM jsonb_each($1) AS obj(key, value));
      ELSE
        RETURN $1;
      END IF;
    END;
    $$ LANGUAGE plpgsql IMMUTABLE;
    """)
    # 创建唯一索引
    op.create_index(
        "idx_unique_jsonb_ignore_order",
        "your_table",
        [sa.text("jsonb_sort(data)")],
        unique=True
    )

def downgrade():
    op.drop_index("idx_unique_jsonb_ignore_order")
    op.execute("DROP FUNCTION IF EXISTS jsonb_sort(jsonb)")

• 双向包含相等逻辑的SQLAlchemy写法
  如果你需要在查询中判断两个JSONB是否忽略顺序相等，直接调用内置的jsonb_contains函数即可：

from sqlalchemy import and_

# a、b可以是JSONB列或者实际的JSONB值
is_equal = and_(func.jsonb_contains(a, b), func.jsonb_contains(b, a))

内容的提问来源于stack exchange，提问作者Alonme