json pgsql 函数 操作_pgsql_json字段使用(复杂的json格式_ json_to_recordset函数的使用)...

第一步:录入数据

DROP TABLE IF EXISTS "public"."business_rate"; CREATE TABLE "public"."business_rate" (

"rid" int8 NOT NULL,

"rate_type" int2 NOT NULL,

"rate_name" varchar(32) COLLATE "default" NOT NULL,

"dt1" int8,

"dt2" int8,

"rate_value" json NOT NULL,

"status" int2 DEFAULT 0 NOT NULL,

"utime" timestamptz(6) DEFAULT now() NOT NULL,

"type" int2 DEFAULT 1 NOT NULL

)

WITH (OIDS=FALSE)

;

COMMENT ON TABLE "public"."business_rate" IS '业务后台费率表';

COMMENT ON COLUMN "public"."business_rate"."rid" IS '主键';

COMMENT ON COLUMN "public"."business_rate"."rate_type" IS '1单点值   2一维   3 二维';

COMMENT ON COLUMN "public"."business_rate"."rate_name" IS '费率名称';

COMMENT ON COLUMN "public"."business_rate"."dt1" IS '第一维度类型';

COMMENT ON COLUMN "public"."business_rate"."dt2" IS '第二维度类型';

COMMENT ON COLUMN "public"."business_rate"."rate_value" IS '费率值，存储json格式';

COMMENT ON COLUMN "public"."business_rate"."status" IS '0正常  1停用  2删除';

COMMENT ON COLUMN "public"."business_rate"."utime" IS '时间';

COMMENT ON COLUMN "public"."business_rate"."type" IS '1按订单笔数结算xx元/笔    2按订单金额占比xx%';

-- ----------------------------

-- Records of business_rate

-- ----------------------------

INSERT INTO "public"."business_rate" VALUES ('23335204448362', '3', '车险费率(线上使用)', '23333337721923', '23333343485088', '[{"name": "北京", "value": [{"name": "太保", "value": "20"}, {"name": "英大", "value": "34"}, {"name": "国寿", "value": "16"}, {"name": "富德", "value": "34"}, {"name": "内蒙古", "value": [{"name": "太保", "value": ""},

{"name": "英大", "value": "27"}, {"name": "国寿", "value": ""},

{"name": "富德", "value": ""}, {"name": "中华联合", "value": "14"},

{"name": "华安", "value": "18"}, {"name": "阳光", "value": "11"},

{"name": "人保", "value": ""}, {"name": "安盛天平", "value": "14"}]},

{"name": "英大", "value": "31"}, {"name": "国寿", "value": ""},

{"name": "富德", "value": ""}, {"name": "中华联合", "value": "14"}]}]', '0', '2016-11-19 14:12:58.677342+08', '2');

-- ----------------------------

-- Alter Sequences Owned By

-- ----------------------------

-- ----------------------------

-- Uniques structure for table business_rate

-- ----------------------------

ALTER TABLE "public"."business_rate" ADD UNIQUE ("rate_name");

-- ----------------------------

-- Checks structure for table business_rate

-- ----------------------------

ALTER TABLE "public"."business_rate" ADD CHECK (((rate_type = 1) AND (dt1 IS NULL) AND (dt2 IS NULL)) OR ((rate_type = 2) AND (dt1 IS NOT NULL) AND (dt2 IS NULL)) OR ((rate_type = 3) AND (dt1 IS NOT NULL) AND (dt2 IS NOT NULL)));

-- ----------------------------

-- Primary Key structure for table business_rate

-- ----------------------------

ALTER TABLE "public"."business_rate" ADD PRIMARY KEY ("rid");

第二步:对rate_value字段进行拆分，解析

SELECT view_.rid,

view_.rate_name,

d.name AS col01,

ge.name AS col02,

ge.value

FROM ( SELECT t.rid,

t.rate_value,

t.rate_name

FROM business_rate t

WHERE t.rate_type = 3 AND t.status = 0) view_,

LATERAL json_to_recordset(view_.rate_value) d(name text, value json),

LATERAL json_to_recordset(d.value) ge(name text, value text);

第三步:执行看拆分效果