背景

遇到这样一个场景，在开发微信开放平台第三方平台的时候，授权的公众号和小程序之间如果需要做到用户打通，需要通过UnionID进行关联，而这个的基础是需要把这些公众号或小程序绑定到他们主体所在的开放平台账号上。所以需要在数据库中有一个表用于记录开放平台的相关信息，如主体名称、开放平台ID和其下属的appid列表，所以需要创建一个列children，以json数组的形式保存这个列表。

那么当有新的账号授权的时候，就需要将其绑定到这个开放平台下，且需要将children列中新增这个账号的AppID。所以就遇到了需要对JSON数组进行增量插入的需求（没有插入，有就不插入，保证数组不重复）

开始

首先我们创建一个表open_platform:

CREATE TABLE IF NOT EXISTS open_platform(
  "id" SMALLSERIAL PRIMARY KEY NOT NULL,
  "principal" varchar(100) NOT NULL,
  "openAppId" varchar(40) NOT NULL,
  "children" jsonb DEFAULT '[]',
  "isCreated" int2 DEFAULT 0,
  CONSTRAINT "unq_1" UNIQUE ("openAppId"),
  CONSTRAINT "unq_2" UNIQUE ("principal", "openAppId")
);
COMMENT ON COLUMN "open_platform"."id" IS 'SERIAL类型自增主键';
COMMENT ON COLUMN "open_platform"."principal" IS '主体名称';
COMMENT ON COLUMN "open_platform"."openAppId" IS '开放平台AppID';
COMMENT ON COLUMN "open_platform"."children" IS '子账号的appid数组';
COMMENT ON COLUMN "open_platform"."isCreated" IS '是否为平台API创建';
COMMENT ON CONSTRAINT "unq_1" ON "open_platform" IS '保证开放平台AppId唯一';
COMMENT ON CONSTRAINT "unq_2" ON "open_platform" IS '保证同一主体的appid唯一，而不是主体名唯一';

然后开始向表中UPSERT数据：

INSERT INTO open_platform( "principal", "openAppId", "children", "isCreated" )
VALUES ( '阿斯麦二', 'aaa', jsonb_build_array ( 'aabbcc' ), 1 )
ON CONFLICT ( "openAppId" ) DO
UPDATE SET children = (
    SELECT TO_JSON(ARRAY_AGG(TTT)) 
    FROM ( SELECT DISTINCT jsonb_array_elements_text( omp_oplatform.children || excluded."children") AS TTT ORDER BY TTT) AS T
) RETURNING id,xmax=0 as isInsert;

知识点

UPSERT操作

详见这篇文章：《传送门》

JSON操作

PGSQL12之前没有提供JSON Path的特性支持，而且也不像MySQL那样提供json_search等查询返回路径的方法，还是有一定的限制的。
另外关于JSONB和JSON，网上大多都是说JSON的写入更快，JSONB的读取更快，却没有说明JSONB比JSON格式多了好几种方法的支持，所以说具体使用哪个格式，最好是提前了解：
《PGSQL官方文档-JSON相关函数》