postgresql实现整改jsonb字段中的某个值

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对象最外层的keyselect 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

（编辑：焦作站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!