正文
plpgsql 数组、JSON相关
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
array_append(anyarray,anyelement) | anyarray | append an element to the end of an array | array_append(ARRAY[1,2], 3) | {1,2,3} |
array_cat(anyarray,anyarray) | anyarray | concatenate two arrays | array_cat(ARRAY[1,2,3], ARRAY[4,5]) | {1,2,3,4,5} |
array_ndims(anyarray) | int | returns the number of dimensions of the array | array_ndims(ARRAY[[1,2,3], [4,5,6]]) | 2 |
array_dims(anyarray) | text | returns a text representation of array's dimensions | array_dims(ARRAY[[1,2,3], [4,5,6]]) | [1:2][1:3] |
array_fill(anyelement,int[], [, int[]]) | anyarray | returns an array initialized with supplied value and dimensions, optionally with lower bounds other than 1 | array_fill(7, ARRAY[3], ARRAY[2]) | [2:4]={7,7,7} |
array_length(anyarray,int) | int | returns the length of the requested array dimension | array_length(array[1,2,3], 1) | 3 |
array_lower(anyarray,int) | int | returns lower bound of the requested array dimension | array_lower('[0:2]={1,2,3}'::int[], 1) | 0 |
array_position(anyarray,anyelement [, int]) | int | returns 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') | 2 |
array_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) | anyarray | append an element to the beginning of an array | array_prepend(1, ARRAY[2,3]) | {1,2,3} |
array_remove(anyarray,anyelement) | anyarray | remove 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) | anyarray | replace each array element equal to the given value with a new value | array_replace(ARRAY[1,2,5,4], 5, 3) | {1,2,3,4} |
array_to_string(anyarray,text [, text]) | text | concatenates array elements using supplied delimiter and optional null string | array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') | 1,2,3,*,5 |
array_upper(anyarray,int) | int | returns upper bound of the requested array dimension | array_upper(ARRAY[1,8,3,7], 1) | 4 |
cardinality(anyarray) | int | returns the total number of elements in the array, or 0 if the array is empty | cardinality(ARRAY[[1,2],[3,4]]) | 4 |
string_to_array(text,text [, text]) | text[] | splits string into array elements using supplied delimiter and optional null string | string_to_array('xx~^~yy~^~zz', '~^~', 'yy') | {xx,NULL,zz} |
unnest(anyarray) | setof anyelement | expand an array to a set of rows | unnest(ARRAY[1,2]) | 1 (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.4 | unnest(ARRAY[1,2],ARRAY['foo','bar','baz']) | 1 foo (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 | 通过键获取值为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数组中的数组。 | select array_to_json('{{1,5},{99,100}}'::int[],true); | [[1,5], + |
| row_to_json(record [, pretty_bool]) | 以JSON对象返回行。如果pretty_bool 为真，则在级别1元素之间添加换行。 | select row_to_json(row(1,'foo'),true); | {"f1":1, + |
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""") |
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) |
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 |
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" c | character varying(32) | select * from json_populate_record(null::tbl_test, '{"a":1,"b":2}'); | a | b | c |
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 |
json_array_elements(json) jsonb_array_elements(jsonb) | setof json setof jsonb | 将json数组转换成json对象value的集合 | select json_array_elements('[1,true, [2,false]]'); | 1 |
json_array_elements_text(json) jsonb_array_elements_text(jsonb) | setof text | 将json数组转换成text的value集合 | select json_array_elements_text('["foo", "bar"]'); | foo |
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 |
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 |
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]'); | [ |
| Function | Description | Example | Example 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
| Function | Return Type | Description | Example | Example Result |
|---|---|---|---|---|
json_array_length(json) jsonb_array_length(jsonb) | int | Returns 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 |
json_each_text(json) jsonb_each_text(jsonb) | setof key text, value text | Expands 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 |
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[]) | text | Returns 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 text | Returns set of keys in the outermost JSON object. | json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') | json_object_keys |
json_populate_record(base anyelement, from_json json) jsonb_populate_record(base anyelement, from_json jsonb) | anyelement | Expands 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 |
json_populate_recordset(base anyelement, from_json json) jsonb_populate_recordset(base anyelement, from_json jsonb) | setof anyelement | Expands 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 |
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 |
json_array_elements_text(json) jsonb_array_elements_text(jsonb) | setof text | Expands a JSON array to a set of text values. | select * from json_array_elements_text('["foo", "bar"]') | value |
json_typeof(json) jsonb_typeof(jsonb) | text | Returns 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) | record | Builds 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 |
json_to_recordset(json) jsonb_to_recordset(jsonb) | setof record | Builds 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 |
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]') | [ |
