上一篇 使用PostgreSQL中的row_to_json()直接获得JSON（PG JSON系列1）主要针对现有的非json/jsonb类型如何输出json结果，这一篇主要写一下在数据库设计中直接使用json/jsonb数据类型（而不是json/jsonb处理函数）带来的问题。我们的问题是：

1. 设定一个典型的JSON/JSONB使用场景，以及演示如何做增删改查

首先来看JSON/JSONB的区别，参考https://www.postgresql.org/docs/9.6/static/datatype-json.html  ：

There are two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; whilejsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

Because the json type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects. Also, if a JSON object within the value contains the same key more than once, all the key/value pairs are kept. (The processing functions consider the last value as the operative one.) By contrast, jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.

最后一句话很明确，基本上使用的时候，都用JSONB。原因可以这么理解json存储是纯粹字符串，jsonb的存储和访问做了解析和优化，等后面我有时间专门写一篇jsonb和json是如何在内核里实现的，以及他们到底有啥区别，此处不做纠结。

首先用JSONB来存储什么呢，我感觉和多维数组相关的比较合适，用ARRAY类型吧，输出的时候还得做个json转换，麻烦。

在数据库设计的时候，有时候为了一小件事情设置个外键关联表有点不值得，感觉有点大材小用，例如下面的演出活动的票价套餐信息(父母帮应该给我广告费，顺便也提一下我们的“两小童”)：

那设计数据库的时候，就有两种方案，一种是常规的做一个关联表（TActivityPricePackage），如下图：

另外一种就是用jsonb来表示票价套餐（pricepackage），这种方法感觉到设计又简洁，还避免获取的时候做JOIN（乖乖，还能减少JAVA程序员工作，是不是也搞好了团队协作了），如下图：

为了简洁的数据库设计的理想而奋斗，下面我们来做个实验：

运行环境：PG 9.6，WIN8.1 64，Tomcat 8.0，JDK 8

数据库表：

测试数据：

好，我分别演示如何做增删改查。

1.先说增

第一次Insert的时候，除了上面测试数据里面直接字符串的方式：

insert into TActivity values(1,'演出活动标题1','[{"packagename":"成人票","price":189,"stock":100},{"packagename":"儿童票（12岁以下）","price":66,"stock":20},{"packagename":"成人+儿童套票","price":128,"stock":10}]');

以外，根据（https://www.postgresql.org/docs/9.6/static/functions-json.html ）还可以有如下方法：

json_build_object

json_object

json_build_array

json_build_object和json_object生成一个名值对的json对象比较方便，而json_build_array只能生成比较简单的json array，正如官方文档例子里所展示的：

综上我们发现，在第一次insert含有数组的时候，直接用字符串吧。

那如果想给jsonb数组增加一条数据怎么办？

我们可以使用连接操作符||：

update tactivity set pricepackage = pricepackage || '{"packagename":"成人票新增3","price":189,"stock":100}'  where id = 1

或者新值在前面：

update tactivity set pricepackage = '{"packagename":"成人票新增3","price":189,"stock":100}' || pricepackage    where id = 1

也可以使用函数jsonb_insert，如在数组的最后增加一个：

update tactivity set pricepackage = jsonb_insert(pricepackage, '{-1}', '{"packagename":"成人票新增100","price":189,"stock":100}', true)    where id = 1

其中-1表示从数组后面数第一个，即倒数第一个，true表示在该元素的后面插入。

或在数组的头部插入：update tactivity set pricepackage = jsonb_insert(pricepackage, '{0}', '{"packagename":"成人票新增00","price":189,"stock":100}', false)    where id = 1

综上我们发现，给jsonb数组最后面或者最前面增加一条数据时，还是使用操作符 || 使用起来更加自然，不易出错。而如果想在jsonb数组的中间增加一条记录，则使用jsonb_insert()。

2.改

如果我们想修改jsonb中的一个数据怎么办？

假设我们现在pricepackage中jsonb数组已有两条数据：

update tactivity set pricepackage = '[{"price":189,"packagename":"成人票0","stock":100},{"price":189,"packagename":"成人票1","stock":100}]'   where id = 1

我们先看第一种情况，如何修改JSONB数组中的第1条完整的记录呢？

1. 即我想把：{"price":189,"packagename":"成人票0","stock":100} 替换成：{"price":189,"packagename":"成人票00","stock":100}

如何写呢？

正常写法应该是：

update tactivity set pricepackage = jsonb_set(pricepackage,'{0}','{"price":189,"packagename":"成人票00","stock":100}',false) where id = 1

但是我发现9.6.0关于jsonb_set()修改数组顶级元素的时候有个bug，参考我的另外一篇博文：

9.6.0版本里jsonb_set()有个bug，截至到2016.10.12

这里的false得替换成true才行，即：

update tactivity set pricepackage = jsonb_set(pricepackage,'{0}','{"price":189,"packagename":"成人票00","stock":100}',true) where id = 1

而在9.5.4里却是正常的，即直接使用false即可。

我们再看第二种情况，如何修改JSONB数组中的第1条记录中的某个值呢？

如，我想把：{"price":189,"packagename":"成人票0","stock":100} 替换成：{"price":189,"packagename":"成人票000","stock":100}

如何写呢？

正常写法应该是：

update tactivity set pricepackage = jsonb_set(pricepackage,'{0,packagename}','"成人票000"',false) where id = 1

这回PG 9.6.0跑对了。

我们再看第三种情况，如果我不知道是数组中的哪一条记录呢？即我想通过一个查找定位到该记录呢，其实这个问题是json的查找定位问题，我们放在JSON的查询那一部分讲。

3.删

官方文档里关于JSONB的删除只有操作符：

这里针对第2，3种情况做个演示：

update tactivity set pricepackage = pricepackage - 0  where id = 1

以及：

update tactivity set pricepackage = pricepackage #- '{0,stock}'  where id = 1

4.查

JSON查找访问方面的操作，我们从字符串匹配/数值比较/ 这两类比较典型的来演示。

假设数据库表TActivity有如下记录：

delete from tactivity;

insert into TActivity values(1,'演出活动标题1','[{"packagename":"成人票","price":189,"stock":100},{"packagename":"儿童票（5-15岁）","price":66,"stock":20},{"packagename":"成人+儿童套票","price":128,"stock":10}]');

insert into TActivity values(2,'演出活动标题2','[{"packagename":"成人票","price":99,"stock":100},{"packagename":"儿童票（3-5岁）","price":58,"stock":20},{"packagename":"成人+儿童套票","price":99,"stock":10}]');

关于字符串匹配，我们演示的问题是：

1. 找出符合条件“packagename = 儿童票（3-5岁）”的那些活动？

那SQL语句为:

select * from TActivity a where a.pricepackage @> '[{"packagename":"儿童票（3-5岁）"}]'::jsonb;

这里的 @> '[{"packagename":"儿童票（3-5岁）"}]'::jsonb，注意必须是'[]'括起来的，即比较时不仅比较内容还要比较结构，参考：https://www.postgresql.org/docs/9.6/static/datatype-json.html  里面的这句话：

The general principle is that the contained object must match the containing object as to structure and data contents, possibly after discarding some non-matching array elements or object key/value pairs from the containing object. But remember that the order of array elements is not significant when doing a containment match, and duplicate array elements are effectively considered only once.

注意上面这句SQL语句，是字符串相等比较，没有通配符的概念，相比于普通字符串比较强大的功能，这或许也是JSONB的缺点（或许以后可以改进）。

关于数值比较，我们演示的问题是：

• 找出符合条件“数组中第0个元素的price < 100”的那些活动？

• 找出符合条件“数组中所有元素的price < 100”的那些活动？
  

关于第1个问题，SQL语句为:

select * from TActivity a where cast(a.pricepackage->0->>'price' as int) <100

关于第2个问题，由于JSONB处理函数不能就数组里面的对象（而不是简单的数值）遍历中比较，所以我们得先把jsonb中的值取出来：

CREATE type json_type_pricepackage AS (packagename text, price numeric, stock numeric);

SELECT id , (jsonb_populate_recordset(null::json_type_pricepackage, pricepackage)).* FROM tactivity;

返回结果为：

然后再做过滤，获得结果：

WITH rows_filtered AS (

   SELECT DISTINCT id FROM (

SELECT id ,(jsonb_populate_recordset(null::json_type_pricepackage, pricepackage)).* FROM tactivity ) a

WHERE price < 100)

SELECT * FROM TActivity a WHERE a.id IN (SELECT * FROM rows_filtered);

是不是有点绕？确实有点。

当然如果不希望用CREATE type json_type_pricepackage AS (packagename text, price numeric, stock numeric);创建类型的话，可以使用函数jsonb_to_recordset()：

select a.id, b.* from tactivity a, jsonb_to_recordset(a.pricepackage) as b(packagename text, price numeric, stock numeric);

然后类似的：

WITH rows_filtered AS (

   SELECT DISTINCT id FROM (

select a.id, b.* from tactivity a, jsonb_to_recordset(a.pricepackage) as b(packagename text, price numeric, stock numeric)

   ) c

WHERE c.price < 100)

SELECT * FROM TActivity a WHERE a.id IN (SELECT * FROM rows_filtered);

来获得想要的结果。

本节小结：我们发现，对于模糊匹配的字符串查询，JSONB还是欠缺的，除非把字符串取出来再做比较，但是又感觉不是很方便。对于数值比较，非==比较，如<，>等比较，还是需要把值从JSONB里取出来在做比较，还是感觉略微不方便。

综上：感觉JSONB更加适合一些特定情况，如字符串模糊检索少，数值非==比较少的情况下。关于JSONB的存储效率和访问效率，专门写一篇博客说明一下。

参考资料：

[1],http://francs3.blog.163.com/blog/static/4057672720157153719971/, francs,PostgreSQL9.5：JSONB 数据类型: 支持元素修改，新增，删除  

码字码了半天，手指头都麻了，如果觉得对您还有用的话，赞助一下辛苦费吧：