| 函数 |
返回类型 |
描述 |
示例 |
结果 |
|
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
]
|
经验首页