Author: zhuyan
PostgreSQL从9.2开始支持Json类型，把它当成标准类型一种，渐渐地提供了12个SQL函数。这篇文章先简单介绍一下Json，然后对于12个函数每一个给出一个执行的例子，最后根据一条SQL语句，从源码角度分析如何执行的。源码那部分跟着代码看效果可能会好很多。
JSON用于描述资料结构，有以下形式存在。
一个Json数据类型的例子：
{
"jobname":"linux_os_vmstat",
"schedule":{
"type":{"interval":
"5m"
},
"start":"now",
"end":"None"
},
"values":{
"event":["cpu_r","cpu_w"],
"data":["cpu_r"],
"threshold":[1,1]
},
"objects":{
"wintest1":"cpu"
}
}
在PostgreSQL 9.2中，增加了Json数据类型和与Json类型相关的两个函数(row_to_json 和array_to_json)。我们可以在PG中像其它类型一样存取Json类型的数据，也可以在数据库中把数据转化为Json数据格式输出。PG中提供几种操作符操纵Json数据，并且在之后的几个版本中，增加了Json相关的函数。
| 操作符 | 右操作数类型 | 描述 | 例子 |
| -> | int | 得到Json数组的元素 | '[1,2,3]'::json->2 |
| -> | text | 得到Json对象的域值 | '{"a":1,"b":2}'::json->'b' |
| ->> | int | 得到Json数组的元素（text格式输出） | '[1,2,3]'::json->>2 |
| ->> | text | 得到Json对象的域值（text格式输出） | '{"a":1,"b":2}'::json->>'b' |
| #> | array of text | 得到指定位置的Json对象 | '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}' |
| #>> | array of text | 得到指定位置的Json对象（text格式输出） | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' |
这是官方文档中的表格，表格中以text格式输出意思是只输出需要的值，而不关心类型。由第一节知道，Json除了Object和Array之外，合法的值有string，number，bool和null。拿string来说，合法的是加双引号，text类型就只有里面的值。在实际使用中输出结果与数据库编码有关，通常使用的是UTF-8类型和ASCII码。混合编码或者其他类型可能导致错误。具体使用在下节例子中会感受的到。
postgreSQL 9.3.6目前支持12个与Json相关的函数操作，可以将这些函数分为两类，一类不操纵Json类型数据，只是提供一个其他类型数据向Json转化的接口(如row_to_json)。另一类就是对Json操作的函数，快速获得其中某些特性(如 json_object_key)。下面就对每一个函数给出一个使用的例子。所有的操作都是基于两张表。一张表job表头（id : int jobdesc : json），其中一行数据如Json简介一节中的示例，用到其它行数据会指明。另一张表films和数据如下：
code | title | did | date_prod | kind | len
-------+-----------+-----+------------+--------+----------
UA502 | Bananas | 105 | 1971-07-13 | Comedy | 01:22:00
UA123 | Apples | 110 | 1999-09-09 | Comedy | 01:44:00
CN111 | Onec More | 111 | 1909-08-11 | Active | 01:54:00
array_to_json(anyarray [, pretty_bool]) 把数组转化成Json类型数据。第一个参数是一个数组，第二个bool类型的表示数组中的元素会不会分行显示。
先用array_agg函数生成数组:
bank=# select array_to_json(array_agg(t)) from (select code,title from films) t;
{"(UA502,Bananas)","(UA123,Apples)","(CN111,\"Onec More\")"}
然后再将数组转化为Json：
bank=# select array_to_json(array_agg(t)) from (select code,title from films) t;
[{"code":"UA502","title":"Bananas"},{"code":"UA123","title":"Apples"},{"code":"CN111","title":"Onec More"}]
第二个参数默认为false,如果为true:
bank=# select array_to_json(array_agg(t),true) from (select code,title from films) t;
[{"code":"UA502","title":"Bananas"},
{"code":"UA123","title":"Apples"},
{"code":"CN111","title":"Onec More"}]
row_to_json(record [, pretty_bool]) 关于row_to_json的妙用可参看这里
select row_to_json(t) from (select code,title from films) t;
{"code":"UA502","title":"Bananas"}
{"code":"UA123","title":"Apples"}
{"code":"CN111","title":"Onec More"}
to_json(anyelement) 其它格式转化为Json
bank=# select to_json(t) from (select code,title from films) t;
{"code":"UA502","title":"Bananas"}
{"code":"UA123","title":"Apples"}
{"code":"CN111","title":"Onec More"}
json_array_length(json)
bank=# select json_array_length(array_to_json(array_agg(t),true)) from (select code,title from films) t;
3
json_each(json) 把一个Json 最外层的Object拆成key-value的形式
bank=# select json_each(to_json(t)) from (select code,title from films where code = 'UA502') t;
(code,"""UA502""")
(title,"""Bananas""")
以这种方式使用，value会多出两个双引号，但是像下面这种方式使用就不会，原因还不太明白。
bank=# select * from json_each( (select jobdesc from job where jobdesc->>'jobname' = 'linux_os_vmstat') );
key | value
----------+----------------------------------
jobname | "linux_os_vmstat"
schedule | {
| "type":{"interval":
| "5m"
| },
| "start":"now",
| "end":"None"
| }
values | {
| "event":["cpu_r","cpu_w"],
| "data":["cpu_r"],
| "threshold":[1,1]
| }
objects | {
| "wintest1":"cpu"
| }
json_each_text(from_json json) 只是输出格式为text
bank=# select json_each_text(to_json(t)) from (select code,title from films where code = 'UA502') t;
(code,UA502)
(title,Bananas)
json_extract_path(from_json json, VARIADIC path_elems text[]) 根据第二个参数提供的路径确定Json中返回的对象。
bank=# select json_extract_path(jobdesc,'objects','wintest1') from job where jobdesc->>'jobname' = 'linux_os_vmstat';
"cpu"
json_extract_path_text(from_json json, VARIADIC path_elems text[])
bank=# select json_extract_path_text(jobdesc,'objects','wintest1') from job where jobdesc->>'jobname' = 'linux_os_vmstat';
cpu
json_object_keys(json) 获得最外层 object的key
bank=# select json_object_keys(jobdesc) from job where jobdesc->>'jobname' = 'linux_os_vmstat';
jobname
schedule
values
objects
json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false] 这个函数较复杂，作用是按照第一个参数定义的数据类型，把第二个参数的Json数据按照这种类型转换输出，第三个参数表示输出为Json类型的话是不是text类型输出。而且这个函数不能处理嵌套的object数据。也就是说key下面value就必须是待转化的值了。一次只能处理一行数据，觉得这个函数在以后版本还有待完善。
首先要定义下类型：
bank=# create type JJ as (jobname text,school text);
本次操作的数据为 {“jobname”:”cs”,”school”:”csu”}
bank=# select* from json_populate_record(null::JJ,(select jobdesc from job where jobdesc->>'jobname' = 'cs'));
jobname | school
---------+--------
cs | csu
11. json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false] 和上一个函数不同之处就在于一次可以处理多行数据。
要处理的数据为：{“jobname”:”cs”,”school”:”csu”} 和 {“jobname”:100,”school”:”csu”}
bank=# select* from json_populate_recordset(null::JJ,(select json_agg(jobdesc) from job where jobdesc->>'school' = 'csu'));
jobname | school
---------+--------
cs | csu
100 | csu
12. json_array_elements(json) 把一个Json数组的每一个元素取出来。
bank=# select * from json_array_elements( (select jobdesc->'values'->'event' from job where jobdesc->>'jobname' = 'linux_os_vmstat') );
"cpu_r"
"cpu_w"
当Json作为一种标准的变长数据类型，进入到内存之后实际上是转化为变长的text类型。存到磁盘上和其他变长数据类型一样，当数据大于2k的时候就会出发toast机制，先对数据试着进行压缩，压缩之后还是大于2kb，就线外存储，放到另一张表里去。
struct varlena
{
char vl_len_[4]; /* Do not touch this field directly! */
char vl_dat[1];
};
这就是变长的存储结构，第一个变量是数据的长度（其实也不是长度，而是长度经过运算处理的结果），第二个变量是数据，只用一个大小为1的char数组表示数据的起始位置。对于变长数据有一系列的宏操作。因此新加入的Json类型操作在内存中都是与text类型进行交互。源码的重点也在于如何解析字符串。
用一个查询语句的执行过程，来分析在源码中是如何处理Json类型数据的。使用job表完成：
select jobdesc->'jobname' from job where jobdesc->>'school' = 'csu';
首先要在缓存中找到job表的元组数据，如果缓存没有就到文件块中取。然后扫描每一个元组数据，得到一个元组数据后就需要调用Json提供的接口对数据进行分析，判断是否有一个名为‘school’的object的域值为‘csu’。如果是则返回名为‘jobname’的object域值给调用端，不是的话就返回空的结果。Json接口只负责返回名为’school ’的object域值，判断域值是否满足条件也由调用端决定。
取出的一个元组数据text类型，需要转化为Json词法分析上下文这种数据结构：
typedef struct JsonLexContext
{
char * input; // 输入的待解析的json字符串，
int input_length; //字符串的长度
char * token_start; //每次分析起始位置 (蓝色)
char * token_terminator; //每次分析的结束位置 (蓝色)
char * prev_token_terminator; //上次分析的结束为止 (蓝色)
JsonTokenType token_type; //分析的字串类型
int lex_level; //分析的“深度” (蓝色)
int line_number; //当前分析到的行数 (红色)
char * line_start; //当前行的起始位置 (红色)
StringInfo strval; //分析得到的结果
} JsonLexContext;
这个结构就像游标一样，遍历一个Json类型数据，对其中每一个object进行解析，得到值再与where子句后面的条件比较，确定是否满足条件。具体是通过makeJsonLexContext这个函数完成由text到JsonLexContext的转化，input指向text转化而来数据的起始位置，表中蓝色为控制变量，在分析过程中不断变化，表示分析过程的状态。红色是为了出错时可以定位到具体的位置。strval为每一次分析得到的临时结果。
除了初始化词法分析上下文，还要初始化最终的保存结果的结构，不同的操作可能对应不同的结构结构（如-> 和 其它SQL函数调用），本例中使用GetState：
typedef struct GetState
{
JsonLexContext *lex; //词法解析上下文
JsonSearch search_type; //搜索类型：object array path
int search_index; //搜索索引
int array_index; //数组索引
char *search_term; //由SQL语句传入的搜索条件的值
char *result_start; //结果起始位置指针（和lexcontext的
//token_terminator一起得到tresult）
text *tresult; //最终结果
bool result_is_null; //结果是否为空
bool normalize_results; //是否是text类型 （由解引操作符得到 如 -> 和 ->>）
bool next_scalar; //函数get_scalar是否可以得到tresult结果（字符串 数值 布尔值）
char path; //路径
int npath; //路径数量
char current_path; //当前分析到的路径指针
bool *pathok; //用bool类型的数组判断走过的路径是否每一步都正确
int *array_level_index; //数组分析深度的指针
int *path_level_index; //路径分析深度的指针
}GetState;
初始化后search_term的值为‘school’,用来进行最后的判断。需要解释下的就是path，请参考上一节函数示例7和8 。path记录的就是函数中的路径。
解析需要判断是否符合条件，并将符合条件的值存近tresult中，这些工作由JsonSemAction完成：
typedef struct JsonSemAction
{
void *semstate;
json_struct_action object_start;
json_struct_action object_end;
json_struct_action array_start;
json_struct_action array_end;
json_ofield_action object_field_start;
json_ofield_action object_field_end;
json_aelem_action array_element_start;
json_aelem_action array_element_end;
json_scalar_action scalar;
} JsonSemAction;
都是一些函数指针，不同类型的函数类型也不一样。semstate是GetState的指针，当然如果是其它类型的state就是其它类型state的指针。
{
"jobname":"linux_os_vmstat", …
}
例: 假如拿到的第一行元组数据为简介中所示数据，调用json_lex函数吃掉第一个”{ ”符号，表明这是一个object，token_type初始化为JSON_TOKEN_OBJECT_START , 再调用parse_object函数，继续推进吃掉“ “ ”号，知道接下来是一个字符串，调用json_lex_string，把值jobname读到strval里面，同时token_type变为JSON_TOKEN_STRING。此时该分析一个object的值域，调用函数parse_object_field，在这个函数中，首先把存在strval里面的值拿出来，再吃掉“：”，之后根据JsonSemAction找到对应类型的处理函数，此处对应的是get_object_field_start，这是函数主要是判断本次解析是否符合条件（也就是strval中的值是否是’school ’）， 根据下一个符号的类型判断是否需要递归。因为一个object的值域可以是一个object，一个array或者一个简单的值，每一种有对应的函数。本例中下一个字符是““ ”，还是string，调用函数parse_scalar，推进JsonLexContext到字符串末尾。最后调用JsonSemAchtion中的get_object_field_end,在这里判断是否符合条件，如果符合就把值域写到tresult中。这也就是一个符合条件的返回结果。如果不符合就继续解析。
当根据where子句的条件找到一个元组变量的时候，就使用select中的条件得到元组变量中对应的值域，解析方式都是相同的。当然不会找到一个就停止，要返回所有满足条件的值，就需要遍历所有的元组。
总的来讲解析用JsonLexContext当作游标，不同类型的state当作结果集变量，JsonSemAction判断是否正确。但并不是所有的操作都是这样，当调用Json 支持的SQL函数的时候，不同的函数都有不同的处理方式，比如row_to_json，得到一行元组变量和它的类型给数据加上{}或者[]等，变成有效的Json格式数据就ok。
http://www.ietf.org/rfc/rfc4627.txt
http://www.postgresql.org/docs/9.3/static/functions-json.html#FUNCTIONS-JSON-OP-TABLE
http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql