2. it
  3. plpgsql 数组、JSON相关

plpgsql 数组、JSON相关

mac2022-07-05  13
FunctionReturn TypeDescriptionExampleResultarray_append(anyarray,anyelement)anyarrayappend an element to the end of an arrayarray_append(ARRAY[1,2], 3){1,2,3}array_cat(anyarray,anyarray)anyarrayconcatenate two arraysarray_cat(ARRAY[1,2,3], ARRAY[4,5]){1,2,3,4,5}array_ndims(anyarray)intreturns the number of dimensions of the arrayarray_ndims(ARRAY[[1,2,3], [4,5,6]])2array_dims(anyarray)textreturns a text representation of array's dimensionsarray_dims(ARRAY[[1,2,3], [4,5,6]])[1:2][1:3]array_fill(anyelement,int[], [, int[]])anyarrayreturns an array initialized with supplied value and dimensions, optionally with lower bounds other than 1array_fill(7, ARRAY[3], ARRAY[2])[2:4]={7,7,7}array_length(anyarray,int)intreturns the length of the requested array dimensionarray_length(array[1,2,3], 1)3array_lower(anyarray,int)intreturns lower bound of the requested array dimensionarray_lower('[0:2]={1,2,3}'::int[], 1)0array_position(anyarray,anyelement [, int])intreturns the subscript of the first occurrence of the second argument in the array, starting at the element indicated by the third argument or at the first element (array must be one-dimensional)array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon')2array_positions(anyarray,anyelement)int[]returns an array of subscripts of all occurrences of the second argument in the array given as first argument (array must be one-dimensional)array_positions(ARRAY['A','A','B','A'], 'A'){1,2,4}array_prepend(anyelement,anyarray)anyarrayappend an element to the beginning of an arrayarray_prepend(1, ARRAY[2,3]){1,2,3}array_remove(anyarray,anyelement)anyarrayremove all elements equal to the given value from the array (array must be one-dimensional)array_remove(ARRAY[1,2,3,2], 2){1,3}array_replace(anyarray,anyelement, anyelement)anyarrayreplace each array element equal to the given value with a new valuearray_replace(ARRAY[1,2,5,4], 5, 3){1,2,3,4}array_to_string(anyarray,text [, text])textconcatenates array elements using supplied delimiter and optional null stringarray_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')1,2,3,*,5array_upper(anyarray,int)intreturns upper bound of the requested array dimensionarray_upper(ARRAY[1,8,3,7], 1)4cardinality(anyarray)intreturns the total number of elements in the array, or 0 if the array is emptycardinality(ARRAY[[1,2],[3,4]])4string_to_array(text,text [, text])text[]splits string into array elements using supplied delimiter and optional null stringstring_to_array('xx~^~yy~^~zz', '~^~', 'yy'){xx,NULL,zz}unnest(anyarray)setof anyelementexpand an array to a set of rowsunnest(ARRAY[1,2]) 1 2 (2 rows)unnest(anyarray, anyarray[, ...])setof anyelement, anyelement [, ...]expand multiple arrays (possibly of different types) to a set of rows. This is only allowed in the FROM clause; see Section 7.2.1.4unnest(ARRAY[1,2],ARRAY['foo','bar','baz']) 1 foo 2 bar NULL baz (3 rows)

以下是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通过键获取值为textselect '{"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?texttext是否作为左侧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"}}'); 

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 | NULL3 | 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"]'); 

foobar

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 | foo2 | 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]

FunctionDescriptionExampleExample Result

to_json(anyelement)

to_jsonb(anyelement)

Returns the value as json or jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json or jsonb value.to_json('Fred said "Hi."'::text)"Fred said \"Hi.\""array_to_json(anyarray [, pretty_bool])Returns the array as a JSON array. A PostgreSQL multidimensional array becomes a JSON array of arrays. Line feeds will be added between dimension-1 elements if pretty_bool is true.array_to_json('{{1,5},{99,100}}'::int[])[[1,5],[99,100]]row_to_json(record [, pretty_bool])Returns the row as a JSON object. Line feeds will be added between level-1 elements if pretty_bool is true.row_to_json(row(1,'foo')){"f1":1,"f2":"foo"}

json_build_array(VARIADIC "any")

jsonb_build_array(VARIADIC "any")

Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list.json_build_array(1,2,'3',4,5)[1, 2, "3", 4, 5]

json_build_object(VARIADIC "any")

jsonb_build_object(VARIADIC "any")

Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.json_build_object('foo',1,'bar',2){"foo": 1, "bar": 2}

json_object(text[])

jsonb_object(text[])

Builds a JSON object out of a text array. The array must have either exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs, or two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair.

json_object('{a, 1, b, "def", c, 3.5}')

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[])

This form of json_object takes keys and values pairwise from two separate arrays. In all other respects it is identical to the one-argument form.json_object('{a, b}', '{1,2}'){"a": "1", "b": "2"}

JSON Processing Functions

FunctionReturn TypeDescriptionExampleExample Result

json_array_length(json)

jsonb_array_length(jsonb)

intReturns the number of elements in the outermost JSON array.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

Expands the outermost JSON object into a set of key/value pairs.select * from json_each('{"a":"foo", "b":"bar"}') key | value -----+------- a | "foo" b | "bar"

json_each_text(json)

jsonb_each_text(jsonb)

setof key text, value textExpands the outermost JSON object into a set of key/value pairs. The returned values will be of type text.select * from json_each_text('{"a":"foo", "b":"bar"}') key | value -----+------- 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

Returns JSON value pointed to by path_elems (equivalent to #> operator).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[])

textReturns JSON value pointed to by path_elems as text (equivalent to #>>operator).json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')foo

json_object_keys(json)

jsonb_object_keys(jsonb)

setof textReturns set of keys in the outermost JSON object.json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') json_object_keys ------------------ f1 f2

json_populate_record(base anyelement, from_json json)

jsonb_populate_record(base anyelement, from_json jsonb)

anyelementExpands the object in from_json to a row whose columns match the record type defined by base (see note below).select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}') a | b ---+--- 1 | 2

json_populate_recordset(base anyelement, from_json json)

jsonb_populate_recordset(base anyelement, from_json jsonb)

setof anyelementExpands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base (see note below).select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]') a | b ---+--- 1 | 2 3 | 4

json_array_elements(json)

jsonb_array_elements(jsonb)

setof json

setof jsonb

Expands a JSON array to a set of JSON values.select * from json_array_elements('[1,true, [2,false]]') value ----------- 1 true [2,false]

json_array_elements_text(json)

jsonb_array_elements_text(jsonb)

setof textExpands a JSON array to a set of text values.select * from json_array_elements_text('["foo", "bar"]') value ----------- foo bar

json_typeof(json)

jsonb_typeof(jsonb)

textReturns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.json_typeof('-123.4')number

json_to_record(json)

jsonb_to_record(jsonb)

recordBuilds an arbitrary record from a JSON object (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause.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] |

json_to_recordset(json)

jsonb_to_recordset(jsonb)

setof recordBuilds an arbitrary set of records from a JSON array of objects (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause.select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text); a | b ---+----- 1 | foo 2 |

json_strip_nulls(from_json json)

jsonb_strip_nulls(from_json jsonb)

json

jsonb

Returns from_json with all object fields that have null values omitted. Other null values are untouched.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

Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true ( default is true) and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)

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

Returns target with new_value inserted. If target section designated by path is in a JSONB array, new_value will be inserted before target or after if insert_after is true (default is false). If target section designated by path is in JSONB object,new_value will be inserted only if target does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')

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

Returns from_json as indented JSON text.jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') [ { "f1": 1, "f2": null }, 2, null, 3 ]

转载于:https://www.cnblogs.com/ssqhan/p/7395857.html

最新回复(0)