JSON基础介绍与PostgreSQL读取JSON字符串的详细流程

PostgreSQL对json的支持已经达到很完善的地步了，这里我是基于阿里云的产品HybirdDB for PostgreSQL来做PostgreSQL的json读取流程(json常识摘自阿里云)

• 检查现有版本是否支持JSON：

SELECT '""'::json;
-- 若系统出现如下信息，则说明已经支持 JSON 类型，可以使用实例了。若执行不成功，请重新启动实例
json 
------
 ""
(1 row)

--若系统出现如下信息，则说明尚未检查JSON类型
ERROR:  type "json" does not exist
LINE 1: SELECT '""'::json;

•  JSON在数据库中的转换

数据库的操作主要分为读和写，JSON 的数据写入一般是字符串到 JSON。字符串中的内容必须符合 JSON 标准，包括字符串、数字、数组、对象等内容

=> SELECT '"hijson"'::json;
 json  
-------
 "hijson"
(1 row)

• JSON内部数据类型定义

-- 对象
  => select '{"key":"value"}'::json;
        json       
  -----------------
   {"key":"value"}
  (1 row)

-- 整数 & 浮点数
  => SELECT '1024'::json;
   json 
  ------
   1024
  (1 row)
  => SELECT '0.1'::json;
   json 
  ------
   0.1
  (1 row)

-- 数组
  => SELECT '[[1,2], [3,4,5]]'::json;
         json       
  ------------------
   [[1,2], [3,4,5]]
  (1 row)

• JSON操作符

=> select oprname,oprcode from pg_operator where oprleft = 3114;
 oprname |          oprcode
---------+---------------------------
 ->      | json_object_field
 ->>     | json_object_field_text
 ->      | json_array_element
 ->>     | json_array_element_text
 #>      | json_extract_path_op
 #>>     | json_extract_path_text_op
(6 rows)

• JSON函数

postgres=# \df *json*
                                                       List of functions
   Schema   |           Name            | Result data type |                    Argument data types                    |  Type
------------+---------------------------+------------------+-----------------------------------------------------------+--------
 pg_catalog | array_to_json             | json             | anyarray                                                  | normal
 pg_catalog | array_to_json             | json             | anyarray, boolean                                         | normal
 pg_catalog | json_array_element        | json             | from_json json, element_index integer                     | normal
 pg_catalog | json_array_element_text   | text             | from_json json, element_index integer                     | normal
 pg_catalog | json_array_elements       | SETOF json       | from_json json, OUT value json                            | normal
 pg_catalog | json_array_length         | integer          | json                                                      | normal
 pg_catalog | json_each                 | SETOF record     | from_json json, OUT key text, OUT value json              | normal
 pg_catalog | json_each_text            | SETOF record     | from_json json, OUT key text, OUT value text              | normal
 pg_catalog | json_extract_path         | json             | from_json json, VARIADIC path_elems text[]                | normal
 pg_catalog | json_extract_path_op      | json             | from_json json, path_elems text[]                         | normal
 pg_catalog | json_extract_path_text    | text             | from_json json, VARIADIC path_elems text[]                | normal
 pg_catalog | json_extract_path_text_op | text             | from_json json, path_elems text[]                         | normal
 pg_catalog | json_in                   | json             | cstring                                                   | normal
 pg_catalog | json_object_field         | json             | from_json json, field_name text                           | normal
 pg_catalog | json_object_field_text    | text             | from_json json, field_name text                           | normal
 pg_catalog | json_object_keys          | SETOF text       | json                                                      | normal
 pg_catalog | json_out                  | cstring          | json                                                      | normal
 pg_catalog | json_populate_record      | anyelement       | base anyelement, from_json json, use_json_as_text boolean | normal
 pg_catalog | json_populate_recordset   | SETOF anyelement | base anyelement, from_json json, use_json_as_text boolean | normal
 pg_catalog | json_recv                 | json             | internal                                                  | normal
 pg_catalog | json_send                 | bytea            | json                                                      | normal
 pg_catalog | row_to_json               | json             | record                                                    | normal
 pg_catalog | row_to_json               | json             | record, boolean                                           | normal
 pg_catalog | to_json                   | json             | anyelement                                                | normal
(24 rows)

• 操作流程

--创建一个表，其中一个字段数据类型是json，如本表的obj
drop table zjgs_dsj.test_json_lqb;
create table zjgs_dsj.test_json_lqb(id serial,obj json, bz VARCHAR);

--插入三个json字符串信息
insert into zjgs_dsj.test_json_lqb(obj) values('{"xx":{
	"name" : "xiaoming",
	"company": "alibaba",
	"title": ["CEO","COO","CTO"]
	}
}');
insert into zjgs_dsj.test_json_lqb(obj) values('{"xx":{
	"name" : "xiaow",
	"company": "huayun",
	"title": ["CEO"]
	}
}');
insert into zjgs_dsj.test_json_lqb(obj) values('{"xx":{
	"name" : "xiaoming",
	"company": "huayun",
	"title": ["CTO"]
	}
}');

--对上述字符串进行切分查找，得出每个人的承担职位个数，后续对数据进行加工
select a.name
			,a.company
			,a.title 
			,LENGTH(a.title)- LENGTH(REPLACE(a.title,',','' )) + 1 as title_num
from (select obj -> 'xx'#>>'{name}' as name
			,obj -> 'xx'#>>'{company}' as company
			,obj -> 'xx'#>>'{title}' as title FROM  zjgs_dsj.test_json_lqb) a




--插入两条json字符串，注意如需使用聚合函数，json字符串头名字必须一致
insert into zjgs_dsj.test_json_lqb(obj) values('{"cyl":{
    "name": "xiaohong",
    "age": "6"
    }
}');

insert into zjgs_dsj.test_json_lqb(obj) values('{"cyl":{
    "name": "xiaolan",
    "age": "12"
    }
}');

--对上述字符串里的年龄进行相加
select sum(cast(obj -> 'cyl'#>>'{age}' as BIGINT)) as sum_age 
from zjgs_dsj.test_json_lqb