PostgreSQL-JSON 数据类型

PostgreSQL-JSON数据类型

前言

  从PostgreSQL 9.3版本开始，JSON已经成为内置数据类型，“一等公民”啦。

  还在羡慕什么文档数据库或者BSON么，赶紧玩玩吧。另外9.4版本，提供JSONB（Binary），提供更多JSON函数和索引支持。

  常用的读取操作符目前大概有三类：->、->>和#>。还是直接看SQL查询的例子吧。

目录

 1. ->例子

 2. ->>例子

 3. #>例子

 4. row_to_json的妙用

1、先看->例子

	postgres=# select '[1,2,3]'::json->2;
	 ?column?
	----------
	 3
	(1 row)
	
	postgres=# select '{"a":1,"b":2}'::json->'b';
	 ?column?
	----------
	 2
	(1 row)

2、再来->>例子

	postgres=# select '[1,2,3]'::json->>2;
	 ?column?
	----------
	 3
	(1 row)
	
	postgres=# select '{"a":1,"b":2}'::json->>'b';
	 ?column?
	----------
	 2
	(1 row)

	有没有发现其实->和->>出来的结果肉眼看起来是一样的？区别在于后者是返回text。

3、再来#>例子

  上面两个操作符实现了读取，其实大部分时候我们的JSON不是这么简单，会内嵌各种数组和哈希，这么读下去会死人的吧。当然，有一种类似path的读取，看例子吧：

	postgres=# select '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}';
	 ?column?
	----------
	 3
	(1 row)
	
	postgres=# select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
	 ?column?
	----------
	 3
	(1 row)


	# 当然里面可以嵌套很多，比如{a,2,b,3}等等。下面再来点表的例子：
	postgres=# create table testjson(id serial, data json);
	postgres=# insert into testjson (data) values('{"a": 1, "b": 2}'::json);
	postgres=# insert into testjson (data) values('{"a": 3, "b": 4, "c": 5}'::json);
	postgres=# insert into testjson (data) values('{"a": 6, "c": 7}'::json);
	
	
	# 插入数据是不是很熟悉，基本和普通使用JSON一致，初窥下select结果：
	postgres=# select * from testjson;
	 id |           data
	----+--------------------------
	  1 | {"a": 1, "b": 2}
	  2 | {"a": 3, "b": 4, "c": 5}
	  3 | {"a": 6, "c": 7}
	(3 rows)
	
			
	# 很眼熟，where条件可以这么用：
	postgres=# select * from testjson where (data->>'a')::int>1;
	 id |           data
	----+--------------------------
	  2 | {"a": 3, "b": 4, "c": 5}
	  3 | {"a": 6, "c": 7}
	(2 rows)
	# 注意这里是->>转换成text然后在::int进行比较。
	
	
	# 不过这里有个坑，不知道怎么解决，比如：
	postgres=# insert into testjson (data) values('{"a": "smallfish"}');
	postgres=# select * from testjson;
	 id |           data
	----+--------------------------
	  1 | {"a": 1, "b": 2}
	  2 | {"a": 3, "b": 4, "c": 5}
	  3 | {"a": 6, "c": 7}
	  5 | {"a": "smallfish"}
	(4 rows)
	# 这个时候上面的::int>1这样就报错了，因为最后一行a为字符串。

4、row_to_json的妙用

The simplest way to return JSON is with row_to_json() function. It accepts a row value and returns a JSON value.

	select row_to_json(words) from words;

This will return a single column per row in the words table.

	{"id":6013,"text":"advancement","pronunciation":"advancement",...}

However, sometimes we only want to include some columns in the JSON instead of the entire row. In theory we could use the row constructor method.

	select row_to_json(row(id, text)) from words;

While this does return only the id and text columns, unfortunately it loses the field names and replaces them with f1, f2, f3, etc.

	{"f1":6013,"f2":"advancement"}

To work around this we must either create a row type and cast the row to that type or use a subquery. A subquery will typically be easier.

	select row_to_json(t)
	from (
	  select id, text from words
	) t

This results in the JSON output for which we would hope:

	{"id":6013,"text":"advancement"}

The other commonly used technique is array_agg and array_to_json. array_agg is a aggregate function like sum or count. It aggregates its argument into a PostgreSQL array. array_to_json takes a PostgreSQL array and flattens it into a single JSON value.

	select array_to_json(array_agg(row_to_json(t)))
	from (
	  select id, text from words
	) t

This will result in a JSON array of objects:

	[{"id":6001,"text":"abaissed"},{"id":6002,"text":"abbatial"},{"id":6003,"text":"abelia"},...]

In exchange for a substantial jump in complexity, we can also use subqueries to return an entire object graph:

	select row_to_json(t)
	from (
	  select text, pronunciation,
	    (
	      select array_to_json(array_agg(row_to_json(d)))
	      from (
	        select part_of_speech, body
	        from definitions
	        where word_id=words.id
	        order by position asc
	      ) d
	    ) as definitions
	  from words
	  where text = 'autumn'
	) t

This could return a result like the following:

	{
	  "text": "autumn",
	  "pronunciation": "autumn",
	  "definitions": [
	    {
	        "part_of_speech": "noun",
	        "body": "skilder wearifully uninfolded..."
	    },
	    {
	        "part_of_speech": "verb",
	        "body": "intrafissural fernbird kittly..."
	    },
	    {
	        "part_of_speech": "adverb",
	        "body": "infrugal lansquenet impolarizable..."
	    }
	  ]
	}

Obviously, the SQL to generate this JSON response is far more verbose than generating it in Ruby. Let's see what we get in exchange.

参考资料

posted @ 2016-12-18 11:30  银河统计  阅读(1333)  评论(0)    收藏  举报