json - Recursive Common Table Expression中的Postgres JSON操作错误

Question

使用这个 Cte:

WITH RECURSIVE "_.catalogIds" AS (
    SELECT "catalog"."name", "catalog"."parent_id", "catalog"."owner_id", "catalog"."image_id", "catalog"."id", "catalog"."created", "catalog"."updated", "catalog"."icon_class" 
    FROM "catalog" 
    WHERE "catalog"."id" = 1
    UNION 
    SELECT "catalog"."name", "catalog"."parent_id", "catalog"."owner_id", "catalog"."image_id", "catalog"."id", "catalog"."created", "catalog"."updated", "catalog"."icon_class" 
    FROM "_.catalogIds"
    INNER JOIN "catalog" ON "catalog"."id" = "_.catalogIds".parent_id
)

在这张 table 上:

CREATE TABLE catalog (
  id         BIGSERIAL,
  created    timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated    timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  name       JSON      NOT NULL,
  parent_id  BIGINT,
  owner_id   BIGINT    NOT NULL,
  image_id   BIGINT,
  icon_class VARCHAR(255)       DEFAULT 'fa fa-book',

  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES catalog (id) ON DELETE CASCADE,
  FOREIGN KEY (owner_id) REFERENCES "user" (id) ON DELETE CASCADE,
  FOREIGN KEY (image_id) REFERENCES media_file (id) ON DELETE SET NULL
);

导致 postgres 报错，它找不到 json 类型的等号运算符。更奇怪的是，它说错误在位置 42，它在语句的选择部分。此外，如果我缩短查询，错误会向后移动，因为它停留在查询的特定位置。我写了很多递归查询，但这个看起来很奇怪。对我来说它看起来像 postgres 错误，但我需要确认和解决方法/工作示例。

Answer 1

UNION 消除重复项，因此查询会比较组件查询的结果行。不幸的是，JSON 类型没有相等运算符，因此无法比较包含该类型列的行。

您可以在两个查询中将该列转换为 JSONB:

    SELECT "catalog"."name"::jsonb, ...

或者如果您不关心重复项，则使用 UNION ALL。