本系列博文讲述使用PG JSON/JSONB类型考虑的问题，这是第一篇（我尽量写的简单，让初学者跟着做很容易也能重复实验），看看不使用JSON/JSONB，仅仅使用row_to_json()函数能帮我们做什么。    

JSON数据类型在当前互联网浪潮中得到了大量的采用，见下图GOOGLE指数对比JSON和XML，从全球范围来看：

蓝色表示JSON的趋势，成明显的上升趋势。

从中国范围来看，JSON搜索热度明显高于XML：

PG也从9.2开始正式引入JSON类型，那自然就问：

1. 既然DB支持了JSON和JSON相关函数，是不是中间服务层（如PHP,JAVA）通过JDBC可以直接获取到JSON数据，而无需再用org.json和json-lib库把以前的行数据进行转换？

我们来做个实验：

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

-- 创建数据库

--postgres登陆pg，使用如下sql创建数据库和用户名密码：

CREATE ROLE jsontest PASSWORD 'jsontest.dba' CREATEDB  NOSUPERUSER CREATEROLE LOGIN;

CREATE DATABASE jsontestdb OWNER jsontest ENCODING 'UTF8';

-- 用jsontest登录，然后是DDL：

-- 创建一个全局的序列

DROP SEQUENCE IF EXISTS jsontest_uuid_seq CASCADE;

CREATE SEQUENCE jsontest_uuid_seq START 1;

-- Table: TUsers, 用户表

DROP TABLE IF EXISTS TUsers CASCADE;

CREATE TABLE TUsers (

 id bigint DEFAULT nextval('jsontest_uuid_seq') PRIMARY KEY,-- 用户id

 realName character varying(64) -- 真实姓名  

)WITH ( OIDS=FALSE );

CREATE INDEX TUsers_cellphone_idx ON TUsers (realName);

-- Table: TProject,用户创建的项目表

DROP TABLE IF EXISTS TProject CASCADE;

CREATE TABLE TProject (

 id bigint DEFAULT nextval('jsontest_uuid_seq') PRIMARY KEY,-- 活动id

 title character varying(256) NOT NULL UNIQUE, -- 项目名称,设置为UNIQUE，避免混淆

 creatorId integer DEFAULT NULL REFERENCES TUsers (id) match simple on delete SET NULL -- 活动创建的用户id  

 )WITH ( OIDS=FALSE );

CREATE INDEX TProject_creator_idx ON TProject( creatorId );

-- 测试数据：

insert into TUsers values(1,'test1');

insert into TUsers values(2,'test2');

insert into TUsers values(3,'test3');

insert into TProject values(1,'测试项目1',1);

insert into TProject values(2,'测试项目2',1);

insert into TProject values(3,'测试项目3',1);

DB设计图（在Eclipse用ERMaster反向生成）如下：

好，JAVA这一块，我们分多种情况来分析：

1. 数据库最多只会返回一行数据，然后转换成JSON对象：如通过用户id获得用户JSON对象

上面的核心是SQL：SELECT row_to_json(a.*) from TUsers a where a.id = 1

那如果我获得的不是a.*，即不需要一整行数据（如密码不想返回）怎么办？如下会报错：

SELECT row_to_json(a.id,a.realName) from TUsers a where a.id = 1

解决办法有：

SELECT row_to_json(a.*) from (select b.id,b.realName from TUsers b where b.id = 1 ) a

或者使用WITH：

WITH myInfo AS (select a.id,a.realName from TUsers a where a.id = 1 )

SELECT row_to_json(b.*) from myInfo b

或者：

SELECT

  (SELECT row_to_json(_) from (select a.id, a.realName) as _)

from

  TUsers a where a.id = 1

关于这个问题可以参考：http://dba.stackexchange.com/questions/27732/set-names-to-attributes-when-creating-json-with-row-to-json

注意的是，不能用row()函数，因为无法产生别名，即正如https://www.postgresql.org/docs/9.6/static/functions-json.html里所演示的那样：

所以综合以上，建议用WITH方法，简洁明了。

public class UserWS extends HttpServlet ：

测试一下，当我们发送：http://localhost:8080/jsonTest/user?action=getuserinfo&userid=1

返回：

{
  "issuccessful":"true",
  "data":{
    "id":1,
    "realname":"test1"
  }
}

2. 数据库返回多行数据：如获取用户参与的项目

WITH myProjects AS (select a.id,a.title from TProject a where a.creatorId = 1)

SELECT row_to_json(b.*) from

(SELECT array_to_json(array(select row_to_json(myProjects.*)  from myProjects),false) as myProjects) b

测试一下：http://localhost:8080/jsonTest/project?action=getmyprojects&userid=1

返回：

{

  "issuccessful":"true",

  "data":{

     "myprojects":[

        {

           "id":3,

           "title":"测试项目3"

        },

        {

           "id":2,

           "title":"测试项目2"

        },

        {

           "id":1,

           "title":"测试项目1"

        }

     ]

  }

}

3. 返回某些表的一行数据，加上某些表的多行数据：如获取用户基本信息以及用户参与的项目

WITH  myInfo AS (select id,realName from TUsers where id = 1 ), -- 一行数据

 myProjects AS (select a.id,a.title from TProject a,myInfo b where a.creatorId = b.id) -- 多行数据

SELECT row_to_json(x.*) from

(

select  c.*,d.*  from

myInfo c,

(SELECT array_to_json(array(select row_to_json(myProjects.*)  from myProjects),false) as myProjects) d -- 把多行数据生成一行一列json数组

) x

该SQL返回：

{
  "myprojects":[
    {
      "id":3,
      "title":"测试项目3"
    },
    {
      "id":2,
      "title":"测试项目2"
    },
    {
      "id":1,
      "title":"测试项目1"
    }
  ],
  "id":1,
  "realname":"test1"
}

总结一下：

仅仅使用PG的row_to_json()，即可轻松返回需要的各种SQL数据。这样子使得中间层（JAVA/PHP）的代码变得简化，无需org.json和json-lib库再做一次封装，这是架构师要注意的一个重要趋势。

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