PostgreSQL从9.2开始支持Json类型，把它当成标准类型一种，渐渐地提供了12个SQL函数。这篇文章先简单介绍一下Json，然后对于12个函数每一个给出一个执行的例子，最后根据一条SQL语句，从源码角度分析如何执行的。源码那部分跟着代码看效果可能会好很多。

Json 简介

JSON用于描述资料结构，有以下形式存在。

  • 物件（object）：一个物件以「{」开始，并以「}」结束。一个物件包含一系列非排序的名称／值对，每个名称／值对之间使用「,」分割。
  • 名称／值（collection）：名称和值之间使用「：」隔开，一般的形式是：{name:value}一个名称是一个字符串； 一个值可以是一个字符串，一个数值，一个物件，一个布尔值，一个有序列表，或者一个null值。
  • 值的有序列表（Array）：一个或者多个值用「,」分割后，使用「[」，「]」括起来就形成了这样的列表，形如：[collection, collection]
  • 字符串：以”“括起来的一串字符。
  • 数值：一系列0-9的数字组合，可以为负数或者小数。还可以用「e」或者「E」表示为指数形式。
  • 布尔值：表示为true或者false。在很多语言中它被解释为阵列。

一个Json数据类型的例子：

  1. {
  2.   "jobname":"linux_os_vmstat",
  3.     "schedule":{
  4.       "type":{"interval":
  5.         "5m"
  6.       },
  7.       "start":"now",
  8.       "end":"None"
  9.     },
  10.     "values":{
  11.       "event":["cpu_r","cpu_w"],
  12.       "data":["cpu_r"],
  13.       "threshold":[1,1]
  14.     },
  15.     "objects":{
  16.       "wintest1":"cpu"
  17.     }
  18. }

二 PostgreSQL 中的Json

在PostgreSQL 9.2中，增加了Json数据类型和与Json类型相关的两个函数(row_to_json 和array_to_json)。我们可以在PG中像其它类型一样存取Json类型的数据，也可以在数据库中把数据转化为Json数据格式输出。PG中提供几种操作符操纵Json数据，并且在之后的几个版本中，增加了Json相关的函数。

2.1 操作符

操作符右操作数类型描述例子
->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码。混合编码或者其他类型可能导致错误。具体使用在下节例子中会感受的到。

2.2 函数

postgreSQL 9.3.6目前支持12个与Json相关的函数操作，可以将这些函数分为两类，一类不操纵Json类型数据，只是提供一个其他类型数据向Json转化的接口(如row_to_json)。另一类就是对Json操作的函数，快速获得其中某些特性(如 json_object_key)。下面就对每一个函数给出一个使用的例子。所有的操作都是基于两张表。一张表job表头（id : int jobdesc : json），其中一行数据如Json简介一节中的示例，用到其它行数据会指明。另一张表films和数据如下：

  1. code  |   title   | did | date_prod  |  kind  |   len
  2. -------+-----------+-----+------------+--------+----------
  3. UA502 | Bananas   | 105 | 1971-07-13 | Comedy | 01:22:00
  4. UA123 | Apples    | 110 | 1999-09-09 | Comedy | 01:44:00
  5. CN111 | Onec More | 111 | 1909-08-11 | Active | 01:54:00

  1. array_to_json(anyarray [, pretty_bool]) 把数组转化成Json类型数据。第一个参数是一个数组，第二个bool类型的表示数组中的元素会不会分行显示。

    先用array_agg函数生成数组:

    1. bank=# select array_to_json(array_agg(t)) from (select code,title from films) t;
    3. {"(UA502,Bananas)","(UA123,Apples)","(CN111,\"Onec More\")"}

    然后再将数组转化为Json：

    1. bank=# select array_to_json(array_agg(t)) from (select code,title from films) t;
    3. [{"code":"UA502","title":"Bananas"},{"code":"UA123","title":"Apples"},{"code":"CN111","title":"Onec More"}]

    第二个参数默认为false,如果为true:

    1. bank=# select array_to_json(array_agg(t),true) from (select code,title from films) t;
    3. [{"code":"UA502","title":"Bananas"},
    4. {"code":"UA123","title":"Apples"},
    5. {"code":"CN111","title":"Onec More"}]

  2. row_to_json(record [, pretty_bool]) 关于row_to_json的妙用可参看这里

    1. select row_to_json(t) from (select code,title from films) t;
    3. {"code":"UA502","title":"Bananas"}
    4. {"code":"UA123","title":"Apples"}
    5. {"code":"CN111","title":"Onec More"}

  3. to_json(anyelement) 其它格式转化为Json

    1. bank=# select to_json(t) from (select code,title from films) t;
    3. {"code":"UA502","title":"Bananas"}
    4. {"code":"UA123","title":"Apples"}
    5. {"code":"CN111","title":"Onec More"}

  4. json_array_length(json)

    1. bank=# select json_array_length(array_to_json(array_agg(t),true)) from (select code,title from films) t;
    3. 3

  5. json_each(json) 把一个Json 最外层的Object拆成key-value的形式

    1. bank=# select json_each(to_json(t)) from (select code,title from films where code = 'UA502') t;
    3. (code,"""UA502""")
    4. (title,"""Bananas""")

    以这种方式使用，value会多出两个双引号，但是像下面这种方式使用就不会，原因还不太明白。

    1. bank=# select * from json_each( (select jobdesc from job where jobdesc->>'jobname' = 'linux_os_vmstat') );
    1.   key    |              value
    2.   ----------+----------------------------------
    3.   jobname  | "linux_os_vmstat"
    4.   schedule | {
    5.         |       "type":{"interval":
    6.         |           "5m"
    7.         |       },
    8.         |       "start":"now",
    9.         |       "end":"None"
    10.         |   }
    11.   values   | {
    12.         |       "event":["cpu_r","cpu_w"],
    13.         |       "data":["cpu_r"],
    14.         |       "threshold":[1,1]
    15.         |   }
    16.   objects  | {
    17.         |       "wintest1":"cpu"
    18.         |   }

  6. json_each_text(from_json json) 只是输出格式为text

    1. bank=# select json_each_text(to_json(t)) from (select code,title from films where code = 'UA502') t;
    3.   (code,UA502)
    4. (title,Bananas)

  7. json_extract_path(from_json json, VARIADIC path_elems text[]) 根据第二个参数提供的路径确定Json中返回的对象。

    1. bank=# select json_extract_path(jobdesc,'objects','wintest1') from job where jobdesc->>'jobname' = 'linux_os_vmstat';
    3. "cpu"

  8. json_extract_path_text(from_json json, VARIADIC path_elems text[])

    1. bank=# select json_extract_path_text(jobdesc,'objects','wintest1') from job where jobdesc->>'jobname' = 'linux_os_vmstat';
    3. cpu

  9. json_object_keys(json) 获得最外层 object的key

    1. bank=# select json_object_keys(jobdesc) from job where jobdesc->>'jobname' = 'linux_os_vmstat';
    3. jobname
    4. schedule
    5. values
    6. objects

  10. json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false] 这个函数较复杂，作用是按照第一个参数定义的数据类型，把第二个参数的Json数据按照这种类型转换输出，第三个参数表示输出为Json类型的话是不是text类型输出。而且这个函数不能处理嵌套的object数据。也就是说key下面value就必须是待转化的值了。一次只能处理一行数据，觉得这个函数在以后版本还有待完善。

首先要定义下类型：

  1.    bank=# create type JJ as (jobname text,school text);

本次操作的数据为 {“jobname”:”cs”,”school”:”csu”}

  1.    bank=# select* from json_populate_record(null::JJ,(select jobdesc from job where jobdesc->>'jobname' = 'cs'));
  4.    jobname | school
  5.    ---------+--------
  6.    cs      | csu
  1. json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false] 和上一个函数不同之处就在于一次可以处理多行数据。

要处理的数据为：{“jobname”:”cs”,”school”:”csu”} 和 {“jobname”:100,”school”:”csu”}

  1.    bank=# select* from json_populate_recordset(null::JJ,(select json_agg(jobdesc) from job where jobdesc->>'school' = 'csu'));
  3.    jobname | school
  4.    ---------+--------
  5.    cs      | csu
  6.    100     | csu
  1. json_array_elements(json) 把一个Json数组的每一个元素取出来。
  1.    bank=# select * from json_array_elements( (select jobdesc->'values'->'event' from job where jobdesc->>'jobname' = 'linux_os_vmstat') );
  2.    "cpu_r"
  3.    "cpu_w"

三 一条查询语句

1. 先谈插入

当Json作为一种标准的变长数据类型，进入到内存之后实际上是转化为变长的text类型。存到磁盘上和其他变长数据类型一样，当数据大于2k的时候就会出发toast机制，先对数据试着进行压缩，压缩之后还是大于2kb，就线外存储，放到另一张表里去。

  1. struct varlena
  2. {
  3.   char    vl_len_[4];   /* Do not touch this field directly! */
  4.   char    vl_dat[1];
  5. };

这就是变长的存储结构，第一个变量是数据的长度（其实也不是长度，而是长度经过运算处理的结果），第二个变量是数据，只用一个大小为1的char数组表示数据的起始位置。对于变长数据有一系列的宏操作。因此新加入的Json类型操作在内存中都是与text类型进行交互。源码的重点也在于如何解析字符串。

2.执行一次查询

用一个查询语句的执行过程，来分析在源码中是如何处理Json类型数据的。使用job表完成：

  1. select jobdesc->'jobname' from job where jobdesc->>'school' = 'csu';

首先要在缓存中找到job表的元组数据，如果缓存没有就到文件块中取。然后扫描每一个元组数据，得到一个元组数据后就需要调用Json提供的接口对数据进行分析，判断是否有一个名为‘school’的object的域值为‘csu’。如果是则返回名为‘jobname’的object域值给调用端，不是的话就返回空的结果。Json接口只负责返回名为’school ’的object域值，判断域值是否满足条件也由调用端决定。

取出的一个元组数据text类型，需要转化为Json词法分析上下文这种数据结构：

  1. typedef struct JsonLexContext
  2. {
  3.   char  * input;             // 输入的待解析的json字符串，
  4.   int input_length;          //字符串的长度
  5.   char  * token_start;           //每次分析起始位置      (蓝色)
  6.   char  * token_terminator;    //每次分析的结束位置    (蓝色)
  7.   char  * prev_token_terminator; //上次分析的结束为止    (蓝色)
  8.   JsonTokenType token_type;          //分析的字串类型
  9.   int lex_level;     //分析的“深度”        (蓝色)
  10.   int line_number;         //当前分析到的行数      (红色)
  11.   char  * line_start;    //当前行的起始位置      (红色)
  12.   StringInfo  strval;            //分析得到的结果
  13. } JsonLexContext;

这个结构就像游标一样，遍历一个Json类型数据，对其中每一个object进行解析，得到值再与where子句后面的条件比较，确定是否满足条件。具体是通过makeJsonLexContext这个函数完成由text到JsonLexContext的转化，input指向text转化而来数据的起始位置，表中蓝色为控制变量，在分析过程中不断变化，表示分析过程的状态。红色是为了出错时可以定位到具体的位置。strval为每一次分析得到的临时结果。

除了初始化词法分析上下文，还要初始化最终的保存结果的结构，不同的操作可能对应不同的结构结构（如-> 和 其它SQL函数调用），本例中使用GetState：

  1. typedef struct GetState
  2. {
  3.   JsonLexContext *lex;    //词法解析上下文
  4.   JsonSearch  search_type;  //搜索类型：object array path
  5.   int search_index;         //搜索索引
  6.   int array_index;    //数组索引
  7.   char  *search_term;           //由SQL语句传入的搜索条件的值
  8.   char  *result_start;          //结果起始位置指针（和lexcontext的
  9.   //token_terminator一起得到tresult）
  10.   text  *tresult;   //最终结果
  11.   bool  result_is_null;         //结果是否为空
  12.   bool  normalize_results;  //是否是text类型 （由解引操作符得到 如  ->  和 ->>）
  13.   bool  next_scalar; //函数get_scalar是否可以得到tresult结果（字符串 数值 布尔值）
  14.   char  path;           //路径
  15.   int npath;            //路径数量
  16.   char  current_path;         //当前分析到的路径指针
  17.   bool  *pathok;    //用bool类型的数组判断走过的路径是否每一步都正确
  18.   int *array_level_index; //数组分析深度的指针
  19.   int *path_level_index;  //路径分析深度的指针
  20. }GetState;

初始化后search_term的值为‘school’,用来进行最后的判断。需要解释下的就是path，请参考上一节函数示例7和8 。path记录的就是函数中的路径。

解析需要判断是否符合条件，并将符合条件的值存近tresult中，这些工作由JsonSemAction完成：

  1. typedef struct JsonSemAction
  2. {
  3.   void     *semstate;
  4.   json_struct_action object_start;
  5.   json_struct_action object_end;
  6.   json_struct_action array_start;
  7.   json_struct_action array_end;
  8.   json_ofield_action object_field_start;
  9.   json_ofield_action object_field_end;
  10.   json_aelem_action array_element_start;
  11.   json_aelem_action array_element_end;
  12.   json_scalar_action scalar;
  13. } JsonSemAction;

都是一些函数指针，不同类型的函数类型也不一样。semstate是GetState的指针，当然如果是其它类型的state就是其它类型state的指针。

  1. {
  2.   "jobname":"linux_os_vmstat",   …
  3. }

例: 假如拿到的第一行元组数据为简介中所示数据，调用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中的条件得到元组变量中对应的值域，解析方式都是相同的。当然不会找到一个就停止，要返回所有满足条件的值，就需要遍历所有的元组。

3. 其它

总的来讲解析用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

http://www.linuxidc.com/Linux/2013-12/94354.htm