postgreSQL 玩转josnb (长期更新)

json和jsonb的操作符

 

jsonb额外操作符

 

 

 

json创建函数

 

 

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的数据类型,可能的类型有

 objectarraystringnumberboolean, 和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
]

posted on 2019-02-21 15:50 复方乙醇消毒液 阅读(...) 评论(...) 编辑 收藏

导航

统计

公告