postgresql----JSON类型和函数
以下内容转⾃：
postgresql⽀持两种json数据类型：json和jsonb，⽽两者唯⼀的区别在于效率,json是对输⼊的完整拷贝，使⽤时再去解析，所以它会保留输⼊的空格，重复键以及顺序等。⽽jsonb是解析输⼊后保存的⼆进制，它在解析时会删除不必要的空格和重复的键，顺序和输⼊可能也不相同。使⽤时不⽤再次解析。两者对重复键的处理都是保留最后⼀个键值对。效率的差别：json类型存储快，使⽤慢，jsonb类型存储稍慢，使⽤较快。
注意：键值对的键必须使⽤双引号
⽰例：
test=# SELECT '{"bar": "baz", "balance":      7.77, "active":false}'::json;
json
------------------------------------------------------
{"bar": "baz", "balance":      7.77, "active":false}
(1 row)
test=# SELECT '{"bar": "baz", "balance":      7.77, "active":false}'::jsonb;
jsonb
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}
(1 row)
测试表：
create table api(jdoc jsonb);
insert into api values('{
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
"name": "Angela Barton",
"is_active": true,
"company": "Magnafone",
"address": "178 Howard Place, Gulf, Washington, 702",
"registered": "2009-11-07T08:53:22 +08:00",
"latitude": 19.793713,
"longitude": 86.513373,
"tags": [
"enim",
"aliquip",
"qui"
]}');
test=# SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';
column                |    column
----------------------------------------+-----------------
"9c36adc1-7fb5-4d5b-83b4-90356a46061a" | "Angela Barton"
(1 row)
jsonb缺省的GIN操作符类⽀持使⽤@>、?、?&和?|操作符查询，在api的jdoc上创建⼀个gin索引。
test=# CREATE INDEX idxgin ON api USING gin (jdoc);
CREATE INDEX
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对象select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';{"c": "foo"}
#>>text[]在指定的路径获取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)
int返回Json数组最外层元素个数 select json_array_length('[1,2,3,
5
jsonb_array_length(jsonb)
{"f1":1,"f2":[5,6]},4]');
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"}}'); f1f2
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]]');
1true [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 最外层value 的数据类
json_typeof(json) jsonb_typeof(jsonb)text
返回json最外层value的数据类json值的类型有哪些
型，可能的类型有
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
]

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。