ios json文件怎么装 pg json

下载客户端：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads  

json和jsonb的区别

        postgresql支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。

注意：键值对的键必须使用双引号

jsonb类型

建表语句

CREATE TABLE "public"."test_json_tbl" (
  "id" int4 ,
  "name" TEXT,
  "json_value" jsonb
);
--设置id为自增序列
CREATE SEQUENCE "public"."test_json_tbl_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 99999999
START 1
CACHE 1;
--设置该序列为此表id主键
alter table "public"."test_json_tbl"  alter column id set default nextval('test_json_tbl_id_seq');
--测试设置序列是否成功
SELECT nextval('public.test_json_tbl_id_seq');
--设置为主键
alter table test_json_tbl add primary key (id) ;

1.插入 

insert into test_json_tbl (name,json_value) 
    VALUES 
('test1','{"name":"jack","home":"江西南昌","values":["a","b"]}')

        往json字符串中添加元素 比如增加年龄字段   备注：如果json_value为json类型则不能这样操作,||是针对jsonb的操作符，意思为拼接，如果jsonb中包含此字段则会进行替换。

update test_json_tbl set json_value=
    (
        SELECT json_value ||'{"age":"12"}'::jsonb FROM test_json_tbl
        where json_value->>'name'='jack'
    )  
where json_value->>'name'='jack';
结果：
id	name	json_value
1	test1	{"age": "12", "home": "江西南昌", "name": "jack", "values": ["a", "b"]}


--替换
select '{"name":"abc"}'::jsonb || '{"name":"aaa"}'::jsonb as name;
 结果：
     {"name": "aaa"}

--新增 或 合并
select '{"name":"abc"}'::jsonb || '{"age":"18"}'::jsonb as person;
结果：
     {"age": "18", "name": "abc"}

如果 select null||'{"name":"hmm"}'  此时是无法合并的，则需要外加一个判断
如：
update test_json_tbl set json_value = (
CASE 
	WHEN json_value is null 
	THEN
		'[{"age":"age", "name":"name"}]'
	ELSE
		json_value || '[{"age":"13", "name":"hmm2"}]'

END
) 
where id=4;
大佬指导：

 update rows of a table Syntax: [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ FROM from_list ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] 
既然它可以是一个 expression， 那下边这个就是可以的： 
update jinbo.jsonb_table1 set extend = ( case when extend is null then '[{"age":"age", "name":"name"}]' else extend || '[{"age":"age", "name":"name"}]' end ) where id = 6; 

2.查询 

select id,name,json_value from test_json_tbl where json_value->>'name'='jack'
结果：id   name     json_value
     1    test1    {"name":"jack","home":"江西南昌","values":["a","b"]}

SELECT json_value->>'home' as home from test_json_tbl where json_value->>'name'='jack'
结果：
    home
    江西南昌

SELECT json_value->>'name' as name FROM test_json_tbl;--返回文本，不带引号
结果：
    name
    jack
SELECT json_value->'name' as name FROM test_json_tbl;--返回json类型,就是原声的，带引号的
结果：
    name
    "jack"
SELECT json_value->'values'#>'{1}' as name FROM test_json_tbl;--获取json的子对象，就是map中key为values中的第1个元素   #>>返回文本
结果：
    name
    "b"

 3.更新

update test_json_tbl set json_value='{"name":"jack2","home":"江西南昌2","values":["a","b"]}' where json_value->>'name1'='jack1'
结果：
    id    name    json_value
    1    test1    {"name":"jack2","home":"江西南昌2","values":["a","b"]}

4.删除

DELETE from test_json_tbl where json_value->>'name'='jack2'

json数组

 json数组的遍历：

INSERT into test_json_tbl ("id","name",json_value) VALUES (2,'humm','
[
	{"age":12,"name":"tom"},
	{"age":12,"name":"tom2"},
	{"age":12,"name":"tom3"}
]');

显示名字集合
SELECT jsonb_array_elements((SELECT json_value FROM test_json_tbl where id=2))#>>'{name}' as namelist;
namelist
tom1
tom2
tom3

json数组元素的插入（增加）

UPDATE test_json_tbl set json_value=json_value||'[{"age": 12, "name": "tom4"}]'::jsonb where id=2;
结果：
[{"age": 12, "name": "tom"}, {"age": 12, "name": "tom2"}, {"age": 12, "name": "tom3"}, {"age": 12, "name": "tom4"}]

备注：->>返回text    一般用于通过key拿到字符串，不带引号

         -> 返回原声对象  一般用于通过key拿到json对象，带引号

          json_array_elements（jsonAry）展开这个json数组的各元素

那如果本身数据库存储的类型字段就是json数组呢？那又该如何在每一行都是json数组找到自己想要的元素呢？（这里枚举两处）

首先先要把每一行的数组转成竖着的，那这里也就是6行了

SELECT id_test_table,jsonb_array_elements(json_value) json_element FROM test_table

那后面只需要加条件就行了，蔽日我要找到age=1的数据

SELECT id_test_table,json_element from 
	(
		SELECT id_test_table,jsonb_array_elements(json_value) json_element FROM test_table
	) t
	WHERE t.json_element->>'age'=1||''

PostgreSQL 9.5以上的版本中有了很多方便的操作符（例如：json字段->>json字段的key   通过key找到对应的json数据）

json和jsonb的操作符

操作符	右操作数类型	描述	示例	结果
->	int	获取JSON数组元素（索引从0开始）	select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;	{"c":"baz"}
->	text	通过键获取值,返回原声对象	select '{"a": {"b":"foo"}}'::json->'a';	{"b":"foo"}
->>	int	获取JSON数组元素为 text	select '[1,2,3]'::json->>2;	3
->>	text	通过键获取值为text	select '{"a":1,"b":2}'::json->>'b';	2
#>	text[] 这里应该写json	在指定的路径获取JSON对象	select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';	{"c": "foo"}
#>>	text[] 这里应该写json	在指定的路径获取JSON对象为 text	select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';	3

jsonb额外操作符

操作符	右操作数类型	描述	示例	结果
@>	jsonb	左侧json最上层的值是否包含右边json对象	select '{"a":{"b":2}}'::jsonb @> '{"b":2}'::jsonb; select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;	f t
<@	jsonb	左侧json对象是否包含于右侧json最上层的值内	select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb;	t
?	text	text是否作为左侧Json对象最上层的键	select '{"a":1, "b":2}'::jsonb ? 'b';	t
?|	text[]	text[]中的任一元素是否作为左侧Json对象最上层的键	select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'];	t
?&	text[]	text[]中的所有元素是否作为左侧Json对象最上层的键	select '["a", "b"]'::jsonb ?& array['a', 'b'];	t
||	jsonb	连接两个json对象，组成一个新的json对象	select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;	["a", "b", "c", "d"]
-	text	删除左侧json对象中键为text的键值对	select '{"a": "b"}'::jsonb - 'a';	{}
-	integer	删除数组指定索引处的元素，如果索引值为负数，则从右边计算索引值。 如果最上层容器内不是数组，则抛出错误。	select '["a", "b"]'::jsonb - 1;	["a"]
#-	text[]	删除指定路径下的域或元素（如果是json数组，且整数值是负的， 则索引值从右边算起）	select '["a", {"b":1}]'::jsonb #- '{1,b}';	["a", {}]

 

json创建函数

函数	描述	示例	结果
to_json(anyelement) to_jsonb(anyelement)	返回json或jsonb类型的值。数组和复合被转换（递归）成数组和对象。另外除数字、 布尔、NULL值（直接使用NULL抛出错误）外，其他标量必须有类型转换。（此处请参考原文）	select to_json('3'::int);	3
array_to_json(anyarray [, pretty_bool])	以JSON数组返回该数组。PostgreSQL多维数组变成JSON数组中的数组。 如果pretty_bool 为真，则在维度1元素之间添加换行。	select array_to_json('{{1,5},{99,100}}'::int[],true);	[[1,5], + [99,100]]
row_to_json(record [, pretty_bool])	以JSON对象返回行。如果pretty_bool 为真，则在级别1元素之间添加换行。	select row_to_json(row(1,'foo'),true);	{"f1":1, + "f2":"foo"}
json_build_array(VARIADIC "any") jsonb_build_array(VARIADIC "any")	建立一个由可变参数列表组成的不同类型的JSON数组	select json_build_array(1,2,'3',4,5);	[1, 2, "3", 4, 5]
json_build_object(VARIADIC "any") jsonb_build_object(VARIADIC "any")	建立一个由可变参数列表组成的JSON对象。参数列表参数交替转换为键和值。	select json_build_object('foo',1,'bar',2);	{"foo" : 1, "bar" : 2}
json_object(text[]) jsonb_object(text[])	根据text[]数组建立一个json对象，如果是一维数组，则必须有偶数个 元素，元素交替组成键和值。如果是二维数组，则每个元素必须有2个元素，可以组成键值对。	select json_object('{a, 1, b, "def", c, 3.5}'); select json_object('{{a, 1},{b, "def"},{c, 3.5}}');	{"a" : "1", "b" : "def", "c" : "3.5"}
json_object(keys text[], values text[]) jsonb_object(keys text[], values text[])	分别从两组text[]中获取键和值，与一维数组类似。	select json_object('{a, b}', '{1,2}');	{"a" : "1", "b" : "2"}

 

json处理函数

函数	返回类型	描述	示例	结果
json_array_length(json) jsonb_array_length(jsonb)	int	返回Json数组最外层元素个数	select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');	5
json_each(json) jsonb_each(jsonb)	setof key text, value json setof key text, value jsonb	将最外层Json对象转换为键值对集合	select json_each('{"a":"foo", "b":"bar"}');	(a,"""foo""") (b,"""bar""")
json_each_text(json) jsonb_each_text(jsonb)	setof key text, value text	将最外层Json对象转换为键值对集合，且value为text类型	select json_each_text('{"a":"foo", "b":"bar"}');	(a,foo) (b,bar)
json_extract_path(from_json json, VARIADIC path_elems text[])   jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])	json jsonb	返回path_elems指向的value，同操作符#>	select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4');	{"f5":99,"f6":"foo"}
json_extract_path_text(from_json json, VARIADIC path_elems text[])   jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])	text	返回path_elems指向的value，并转为text类型，同操作符#>>	select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6');	foo
json_object_keys(json) jsonb_object_keys(jsonb)	setof text	返回json对象最外层的key	select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');	f1 f2
json_populate_record(base anyelement, from_json json)   jsonb_populate_record(base anyelement, from_json jsonb)	anyelement	将json对象的value以base定义的行类型返回，如果行类型字段比json对象键值少，则多出的键值将被抛弃；如果行类型字段多，则多出的字段自动填充NULL。	表tbl_test定义：   Table "public.tbl_test" Column | Type | Modifiers  --------+-----------------------+----------- a | bigint |  b | character varying(32) | c | character varying(32) |   select * from json_populate_record(null::tbl_test, '{"a":1,"b":2}');	a |  b |  c ---+---+------ 1 | 2  | NULL
json_populate_recordset(base anyelement, from_json json)   jsonb_populate_recordset(base anyelement, from_json jsonb)	setof anyelement	将json对象最外层数组以base定义的行类型返回	表定义同上  select * from json_populate_recordset(null::tbl_test, '[{"a":1,"b":2},{"a":3,"b":4}]');	a | b |  c  ---+---+------ 1 | 2 | NULL 3 | 4 | NULL
json_array_elements(json) jsonb_array_elements(jsonb)	setof json setof jsonb	将json数组转换成json对象value的集合	select json_array_elements('[1,true, [2,false]]');	1 true [2,false]
json_array_elements_text(json) jsonb_array_elements_text(jsonb)	setof text	将json数组转换成text的value集合	select json_array_elements_text('["foo", "bar"]');	foo bar
json_typeof(json) jsonb_typeof(jsonb)	text	返回json最外层value的数据类型，可能的类型有  object, array, string, number, boolean, 和null.	select json_typeof('-123.4')	number
json_to_record(json) jsonb_to_record(jsonb)	record	根据json对象创建一个record类型记录，所有的函数都返回record类型，所以必须使用as明确定义record的结构。	select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text);	a |    b    |   d  ---+---------+------ 1 | [1,2,3] | NULL
json_to_recordset(json) jsonb_to_recordset(jsonb)	setof record	根据json数组创建一个record类型记录，所有的函数都返回record类型，所以必须使用as明确定义record的结构。	select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);	a | b  ---+------ 1 | foo 2 | NULL
json_strip_nulls(from_json json) jsonb_strip_nulls(from_json jsonb)	json jsonb	返回json对象中所有非null的数据，其他的null保留。	select json_strip_nulls('[{"f1":1,"f2":null},2,null,3]');	[{"f1":1},2,null,3]
jsonb_set(target jsonb, path text[],new_value jsonb[,create_missing boolean])	jsonb	如果create_missing为true，则将在target的path处追加新的jsonb；如果为false，则替换path处的value。	select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);   select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');	[{"f1": [2, 3, 4], "f2": null}, 2, null, 3]   [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean])	jsonb	如果insert_after是true，则在target的path后面插入新的value，否则在path之前插入。	select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');   select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true);	{"a": [0, "new_value", 1, 2]}   {"a": [0, 1, "new_value", 2]}
jsonb_pretty(from_json jsonb)	text	以缩进的格式更容易阅读的方式返回json对象	select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]');	[    {     "f1": 1,    "f2": null   },    2,   null,   3 ]