博文
PG中验证JSON Schema（PG JSON系列4）
|||
上一篇写过 组合使用PostgreSQL的ARRAY和JSONB数据类型（PG JSON系列3） ，这一篇写一下如何在PG中验证json字符串的schema。好比在上一篇博文中我们设计了：
-- Table: TPIC -- 照片表 DROP TABLE IF EXISTS TPIC CASCADE; CREATE TABLE TPIC ( id bigint DEFAULT nextval('jsontest_uuid_seq') PRIMARY KEY,-- 活动id,全局唯一性，用于外键关联 picUrl character varying(256) DEFAULT NULL,-- 照片 persons jsonb NOT NULL -- 照片里的人名、电话等，格式为：[{"position":"前1排左1","name":"张晓陆","phone":"18910180100","city":"济南","organization":"中科院计算所","attendanceYear":"2012","graduationYear":"2016"},{"position":"前1排左2","name":"张晓陆","phone":"18910180100","organization":"中科院计算所","attendanceYear":"2012","graduationYear":"2016"}] )WITH ( OIDS=FALSE); |
我们期望每次修改persons的时候，都是符合或者遵循如下类似数据格式的：
{ "position": "前1排左1", "name": "张晓陆", "phone": "18910180100", "city": "济南", "organization": "中科院计算所", "attendanceYear": "2012", "graduationYear": "2016" } |
而不是随意的格式，这就带来一个问题：
如何让PG验证JSON的Schema？
我们先来看什么是JSON的Schema？根据http://json-schema.org/ 官方描述，
The JSON document being validated or described we call the instance, and the document containing the description is called the schema.
以及在https://spacetelescope.github.io/understanding-json-schema/# ：
JSON Schema is a powerful tool for validating the structure of JSON data.
简单来讲，JSON文档就是个实例（instance），Schema描述该实例的数据结构。
1. 我们先看看，如果不在PG中验证JSON文档的Schema，而是在应用层中（如JAVA）如何做验证（Validate）？
JAVA中插入或更新PG中JSONB数据时，既可以自己拼接一个JSON对象，也可以直接由客户端传入一个JSON对象。前者无需Validate，毕竟是自己在服务端控制的，后者则需要。
首先，我们通过http://jsonschema.net/#/ 生成persons对应的Schema：
{ "$schema": "http://json-schema.org/draft-04/schema#", "type": "object", "properties": { "position": { "type": "string" }, "name": { "type": "string" }, "phone": { "type": "string" }, "city": { "type": "string" }, "organization": { "type": "string" }, "attendanceYear": { "type": "string" }, "graduationYear": { "type": "string" } }, "required": [ "position", "name", "phone", "city", "organization", "attendanceYear", "graduationYear" ] } |
当然，如果你有时间，好好研究一下https://spacetelescope.github.io/understanding-json-schema/# ，自己手写更加复杂的Schema。你也可以通过http://json-schema-validator.herokuapp.com/ 先自己验证一下。
我们使用的是https://github.com/everit-org/json-schema + https://github.com/stleary/JSON-java 库 ,而没使用：https://github.com/daveclayton/json-schema-validator + https://github.com/FasterXML/jackson 库，后者尝试用 http://wilddiary.com/validate-json-against-schema-in-java/ 方法一直没成功。
故还是用https://github.com/everit-org/json-schema 方法吧。JAVA代码为（新的Dynamic Website Project）：
package com.yingziedu.util; import java.io.IOException; import java.io.InputStream; import org.everit.json.schema.Schema; import org.everit.json.schema.ValidationException; import org.everit.json.schema.loader.SchemaLoader; import org.json.JSONException; import org.json.JSONObject; import org.json.JSONTokener; public class JsonUtil { public Boolean validate(String jsonInstance){ Boolean res = false; try ( InputStream inputStream = getClass().getResourceAsStream("personSchema.json")) { JSONObject rawSchema = new JSONObject(new JSONTokener(inputStream)); Schema schema = SchemaLoader.load(rawSchema); schema.validate(new JSONObject(jsonInstance)); // throws a ValidationException if this object is invalid res = true; } catch (IOException e) { e.printStackTrace(); } catch (JSONException e) { e.printStackTrace(); } catch (ValidationException e) { e.printStackTrace(); } return res; } } |
完整的代码：http://pan.baidu.com/s/1pLTLXF1 。
当我们输入一个错误的JSON文档时，例如把：
{
"position": "前1排左1",
写成了：
{
"position1": "前1排左1",
则，我们会得到验证异常为：
org.everit.json.schema.ValidationException: #: required key [position] not found
好，JAVA的例子就写到这里。
2. 接下来我们看看，在PG中能否验证JSON文档的Schema？
首先，我们查一下pg mailing list，看看有没？发现： https://www.postgresql.org/message-id/25D7D080-A3D5-4C3D-AE2E-E2B7BC2655AF@gmail.com 里有一个回复：
> On 04 May 2015, at 06:20, Dmitry Shirokov <deadrunk(at)gmail(dot)com> wrote: > > Hi all, > > Are there any plans to introduce in next versions of Postgres a schema validation for JSON field type? It would be very nice to have a support of something like json-schema spec, see http://json-schema.org/documentation.html. Right now there's the only way to do it via individual constraints, which is not very convenient in most cases. Please correct me if I'm wrong. > Take a look at https://github.com/akorotkov/jsquery You can find schema validation example in docs. > Cheers, > Dmitry |
好，那我们编译安装该https://github.com/akorotkov/jsquery 扩展，测试一下。我们的环境是CENTOS7。
首先下载下来jsquery-master.zip，解压缩，重新jsquery-master命名为jsquery目录。
把postgresql 9.6的源码下载下来，把jsquery拷贝到postgresql的contribute目录下，修改postgresql-9.6.0/contrib/Makefile,把SUBDIRS里面增加一项jsquery，然后：
cd postgresql-9.6.0
./configure
make world
make install-world
然后createdb之后：
CREATE EXTENSION jsquery;
然后就可以了:
test=# create table tpic(
id serial,
picUrl character varying(256) default NULL,
persons jsonb NOT NULL,
check(persons @@ '#:(
persons IS STRING AND
name IS STRING AND
phone IS STRING AND
city IS STRING AND
organization IS STRING AND
attendanceYear IS STRING AND
graduationYear IS STRING
)'::jsquery)
);
test=# insert into tpic values(1,null,'[{"persons":"前1排左1","name": "张晓陆","phone": "18910180100","city": "济南","organization": "中科院计算所","attendanceYear": "2012","graduationYear": "2016"}]');
INSERT 0 1
然后我们再用违反Schema约束的graduationYear=3试试：
test=# update tpic set persons = persons || '[{"persons":"前1排左2","name": "张 晓陆2","phone": "18910180102","city": "济南2","organization": "中科院计算所2","attendanceYear": "2013","graduationYear": 3}]';
ERROR: new row for relation "tpic" violates check constraint "tpic_persons_check"
DETAIL: Failing row contains (1, null, [{"city": "济南", "name": "张晓陆", "phone": "18910180100",...).
果然，check起作用了。
结论：感觉jsquery还是挺好用的，可以值得尝试。
码字码了半天，手指头都麻了，如果觉得对您还有用的话，赞助一下辛苦费吧：
https://blog.sciencenet.cn/blog-419883-1009248.html
上一篇：收藏一个PG查询计划树分析的在线工具
下一篇：PG中JSONB存储和访问效率（PG JSON系列5）
