我的书签
添加书签
移除书签JSON/JSONB函数和操作符
JSON/JSONB数据类型参考JSON/JSONB类型。
表 1 JSON/JSONB通用操作符
| 操作符 | 左操作数类型 | 右操作数类型 | 返回类型 | 描述 | 例子 | 例子结果 |
|---|---|---|---|---|---|---|
| -> | Array-json(b) | int | json(b) | 获得array-json元素。下标不存在返回空。 | ‘[{“a”:”foo”},{“b”:”bar”},{“c”:”baz”}]’::json->2 | {“c”:”baz”} |
| -> | object-json(b) | text | json(b) | 通过键获得值。不存在则返回空。 | ‘{“a”: {“b”:”foo”}}’::json->’a’ | {“b”:”foo”} |
| ->> | Array-json(b) | int | text | 获得 JSON 数组元素。下标不存在返回空。 | ‘[1,2,3]’::json->>2 | 3 |
| ->> | object-json(b) | text | text | 通过键获得值。不存在则返回空。 | ‘{“a”:1,”b”:2}’::json->>’b’ | 2 |
| #> | container-json (b) | text[] | json(b) | 获取在指定路径的 JSON 对象，路径不存在则返回空。 | ‘{“a”: {“b”:{“c”: “foo”}}}’::json #>’{a,b}’ | {“c”: “foo”} |
| #>> | container-json (b) | text[] | text | 获取在指定路径的 JSON 对象，路径不存在则返回空。 | ‘{“a”:[1,2,3],”b”:[4,5,6]}’::json #>>’{a,2}’ | 3 |
注意： 对于 #> 和 #>> 操作符，当给出的路径无法查找到数据时，不会报错，会返回空。
表 2 JSONB额外支持操作符
| 操作符 | 右操作数类型 | 描述 | 例子 |
|---|---|---|---|
| @> | jsonb | 左边的 JSON的顶层是否包含右边JSON的顶层所有项。 | ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb |
| <@ | jsonb | 左边的 JSON的所有项是否全部存在于右边JSON的顶层。 | ‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb |
| ? | text | 键/元素的字符串是否存在于 JSON 值的顶层。 | ‘{“a”:1, “b”:2}’::jsonb ? ‘b’ |
| ? | text[] | 这些数组字符串中的任何一个是否做为顶层键存在。 | |
| ?& | text[] | 是否所有这些数组字符串都作为顶层键存在。 | ‘[“a”, “b”]’::jsonb ?& array[‘a’, ‘b’] |
| = | jsonb | 判断两个jsonb的大小关系，同函数jsonb_eq。 | / |
| <> | jsonb | 判断两个jsonb的大小关系，同函数jsonb_ne。 | / |
| < | jsonb | 判断两个jsonb的大小关系，同函数jsonb_lt。 | / |
| > | jsonb | 判断两个jsonb的大小关系，同函数jsonb_gt。 | / |
| <= | jsonb | 判断两个jsonb的大小关系，同函数jsonb_le。 | / |
| >= | jsonb | 判断两个jsonb的大小关系，同函数jsonb_ge。 | / |
JSON/JSONB支持的函数
- array_to_json(anyarray [, pretty_bool])
描述：返回JSON类型的数组。一个多维数组成为一个JSON数组的数组。如果pretty_bool为true，将在一维元素之间添加换行符。
返回类型：json
示例：
1. openGauss=# SELECT array_to_json('{{1,5},{99,100}}'::int[]);
2. array_to_json
3. ------------------
4. [[1,5],[99,100]]
5. (1 row)
- row_to_json(record [, pretty_bool])
描述：返回JSON类型的行。如果pretty_bool为true，将在第一级元素之间添加换行符。
返回类型：json
示例：
1. openGauss=# SELECT row_to_json(row(1,'foo'));
2. row_to_json
3. ---------------------
4. {"f1":1,"f2":"foo"} (1 row)
- json_array_element(array-json, integer)、jsonb_array_element(array-jsonb, integer)
描述：同操作符->, 返回数组中指定下标的元素。
返回类型：json、jsonb
示例：
1. openGauss=# select json_array_element('[1,true,[1,[2,3]],null]',2);
2. json_array_element
3. --------------------
4. [1,[2,3]]
5. (1 row)
- json_array_element_text(array-json, integer)、jsonb_array_element_text(array-jsonb, integer)
描述：同操作符->>, 返回数组中指定下标的元素。
返回类型：text、text
示例：
1. openGauss=# select json_array_element_text('[1,true,[1,[2,3]],null]',2);
2. json_array_element_text
3. -----------------------
4. [1,[2,3]]
5. (1 row)
- json_object_field(object-json, text)、jsonb_object_field(object-jsonb, text)
描述：同操作符->, 返回对象中指定键对应的值。
返回类型：json、json
示例：
1. openGauss=# select json_object_field('{"a": {"b":"foo"}}','a');
2. json_object_field
3. -------------------
4. {"b":"foo"}
5. (1 row)
- json_object_field_text(object-json, text)、jsonb_object_field_text(object-jsonb, text)
描述：同操作符->>, 返回对象中指定键对应的值。
返回类型：text、text
示例：
1. openGauss=# select json_object_field_text('{"a": {"b":"foo"}}','a');
2. json_object_field_text
3. ----------------------
4. {"b":"foo"}
5. (1 row)
- json_extract_path(json, VARIADIC text[])、jsonb_extract_path((jsonb, VARIADIC text[])
描述：等价于操作符#>。根据$2所指的路径，查找json，并返回。
返回类型：json、jsonb
示例：
1. openGauss=# select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
2. json_extract_path
3. -------------------
4. "stringy"
5. (1 row)
- json_extract_path_op(json, text[])、jsonb_extract_path_op(jsonb, text[])
描述：同操作符#>。根据$2所指的路径，查找json，并返回。
返回类型：json、jsonb
示例：
1. openGauss=# select json_extract_path_op('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
2. json_extract_path_op
3. ---------------------
4. "stringy"
5. (1 row)
- json_extract_path_text(json, VARIADIC text[])、jsonb_extract_path_text((jsonb, VARIADIC text[])
描述：等价于操作符#>>。根据$2所指的路径，查找json，并返回。
返回类型：text、text
示例：
1. openGauss=# select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
2. json_extract_path_text
3. -----------------------
4. "stringy"
5. (1 row)
- json_extract_path_text_op(json, text[])、jsonb_extract_path_text_op(jsonb, text[])
描述：同操作符#>>。根据$2所指的路径，查找json，并返回。
返回类型：text、text
示例：
1. openGauss=# select json_extract_path_text_op('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
2. json_extract_path_text_op
3. --------------------------
4. "stringy"
5. (1 row)
- json_array_elements(array-json)、jsonb_array_elements(array-jsonb)
描述：拆分数组，每一个元素返回一行。
返回类型：json、jsonb
示例：
1. openGauss=# select json_array_elements('[1,true,[1,[2,3]],null]');
2. json_array_elements
3. ---------------------
4. 1
5. true
6. [1,[2,3]]
7. null
8. (4 rows)
- json_array_elements_text(array-json)、jsonb_array_elements_text(array-jsonb)
描述：拆分数组，每一个元素返回一行。
返回类型：text、text
示例：
``` 1. openGauss=# select * from json_array_elements_text('[1,true,[1,[2,3]],null]'); 2. value 3. ----------- 4. 1 5. true 6. [1,[2,3]]
- (4 rows) ```
- json_array_length(array-json)、jsonb_array_length(array-jsonb)
描述：返回数组长度。
返回类型：integer
示例：
1. openGauss=# SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4,null]');
2. json_array_length
3. -------------------
4. 6
5. (1 row)
- json_each(object-json)、jsonb_each(object-jsonb)
描述：将对象的每个键值对拆分转换成一行两列。
返回类型：setof(key text, value json)、setof(key text, value jsonb)
示例：
1. openGauss=# select * from json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
2. key | value
3. -----+----------
4. f1 | [1,2,3]
5. f2 | {"f3":1}
6. f4 | null
7. (3 rows)
- json_each_text(object-json)、jsonb_each_text(object-jsonb)
描述：将对象的每个键值对拆分转换成一行两列。
返回类型：setof(key text, value text)、setof(key text, value text)
示例：
1. openGauss=# select * from json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
2. key | value
3. -----+----------
4. f1 | [1,2,3]
5. f2 | {"f3":1}
6. f4 |
7. (3 rows)
- json_object_keys(object-json)、jsonb_object_keys(object-jsonb)
描述：返回对象中顶层的所有键。
返回类型：SETOF text
示例：
1. openGauss=# select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
2. json_object_keys
3. ------------------
4. f1
5. f2
6. f1
7. (3 rows)
- jsonb中会有去重操作
1. openGauss=# select jsonb_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
2. jsonb_object_keys
3. -------------------
4. f1
5. f2
6. (2 rows)
- json_populate_record(anyelement, object-json [, bool])、jsonb_populate_record(anyelement, object-jsonb [, bool])
描述：$1必须是一个复合类型的参数。将会把object-json里的每个对键值进行拆分，以键当做列名，与$1中的列名进行匹配查找，并填充到$1的格式中。
返回类型：anyelement、anyelement
示例：
1. openGauss=# create type jpop as (a text, b int, c bool);
2. CREATE TYPE
3. postgres=# select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}');
4. a | b | c
5. --------+---+---
6. blurfl | |
7. (1 row)
1. openGauss=# select * from json_populate_record((1,1,null)::jpop,'{"a":"blurfl","x":43.2}');
2. a | b | c
3. --------+---+---
4. blurfl | 1 |
5. (1 row)
- json_populate_record_set(anyelement, array-json [, bool])、jsonb_populate_record_set(anyelement, array-jsonb [, bool])
描述：参考上述函数json_populate_record、jsonb_populate_record，对$2数组的每一个元素进行上述参数函数的操作，因此这也要求$2数组的每个元素都是object-json类型的。
返回类型：setof anyelement、setof anyelement
示例：
1. openGauss=# create type jpop as (a text, b int, c bool);
2. CREATE TYPE
3. postgres=# select * from json_populate_recordset(null::jpop, '[{"a":1,"b":2},{"a":3,"b":4}]');
4. a | b | c
5. ---+---+---
6. 1 | 2 |
7. 3 | 4 |
8. (2 rows)
- json_typeof(json)、jsonb_typeof(jsonb)
描述：检测json类型
返回类型：text、text
示例：
1. openGauss=# select value, json_typeof(value)
2. postgres-# from (values (json '123.4'), (json '"foo"'), (json 'true'), (json 'null'), (json '[1, 2, 3]'), (json '{"x":"foo", "y":123}'), (NULL::json)) as data(value);
3. value | json_typeof
4. ----------------------+-------------
5. 123.4 | number
6. "foo" | string
7. true | boolean
8. null | null
9. [1, 2, 3] | array
10. {"x":"foo", "y":123} | object
11. |
12. (7 rows)
- json_build_array( [VARIADIC “any”] )
描述：从一个可变参数列表构造出一个JSON数组。
返回类型：array-json
示例：
1. openGauss=# select json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}','');
2. json_build_array
3. ---------------------------------------------------------------------------
4. ["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1,2,3]}, ""]
5. (1 row)
- json_build_object( [VARIADIC “any”] )
描述：从一个可变参数列表构造出一个JSON对象，其入参必须为偶数个，两两一组组成键值对。注意键不可为null。
返回类型：object-json
示例：
1. openGauss=# select json_build_object(1,2);
2. json_build_object
3. -------------------
4. {"1" : 2}
5. (1 row)
- json_to_record(object-json, bool)
描述：正如所有返回record 的函数一样，调用者必须用一个AS子句显式地定义记录的结构。会将object-json的键值对进行拆分重组，把键当做列名，去匹配填充as显示指定的记录的结构。
返回类型：record
示例：
1. openGauss=# select * from json_to_record('{"a":1,"b":"foo","c":"bar"}',true) as x(a int, b text, d text);
2. a | b | d
3. ---+-----+---
4. 1 | foo |
5. (1 row)
- json_to_recordset(array-json, bool)
描述：参考函数json_to_record，对数组内个每个元素，执行上述函数的操作，因此这要求数组内的每个元素都得是object-json。
返回类型：setof record
示例：
1. openGauss=# select * from json_to_recordset(
2. openGauss(# '[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',
3. openGauss(# false
4. openGauss(# ) as x(a int, b text, c boolean);
5. a | b | c
6. ---+-----+---
7. 1 | foo |
8. 2 | bar | t
9. (2 rows)
- json_object(text[])、json_object(text[], text[])
描述：从一个文本数组构造一个object-json。这是个重载函数，当入参为一个文本数组的时候，其数组长度必须为偶数，成员被当做交替出现的键/值对。两个文本数组的时候，第一个数组认为是键，第二个认为是值，两个数组长度必须相等。键不可为null。
返回类型：object-json
示例：
1. openGauss=# select json_object('{a,1,b,2,3,NULL,"d e f","a b c"}');
2. json_object
3. -------------------------------------------------------
4. {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"}
5. (1 row)
1. postgres=# select json_object('{a,b,"a b c"}', '{a,1,1}');
2. json_object
3. ---------------------------------------
4. {"a" : "a", "b" : "1", "a b c" : "1"}
5. (1 row)
- json_agg(any)
描述：将值聚集为json数组。
返回类型：array-json
示例：
1. openGauss=# select * from classes;
2. name | score
3. -----+-------
4. A | 2
5. A | 3
6. D | 5
7. D |
8. (4 rows)
1. openGauss=# select name, json_agg(score) score from classes group by name order by name;
2. name | score
3. -----+-----------------
4. A | [2, 3]
5. D | [5, null]
6. | [null]
7. (3 rows)
- json_object_agg(any, any)
描述：将值聚集为json对象。
返回类型：object-json
示例：
1. openGauss=# select * from classes;
2. name | score
3. -----+-------
4. A | 2
5. A | 3
6. D | 5
7. D |
8. (4 rows)
1. openGauss=# select json_object_agg(name, score) from classes group by name order by name;
2. json_object_agg
3. -------------------------
4. { "A" : 2, "A" : 3 }
5. { "D" : 5, "D" : null }
6. (2 rows)
- jsonb_contained(jsonb, jsonb)
描述：同操作符 <@, 判断$1中的所有元素是否在$2的顶层存在。
返回类型：bool
示例：
1. openGauss=# select jsonb_contained('[1,2,3]', '[1,2,3,4]');
2. jsonb_contained
3. -----------------
4. t
5. (1 row)
- jsonb_contains(jsonb, jsonb)
描述：同操作符 @>, 判断$1中的顶层所有元素是否包含在$2的所有元素。
返回类型：bool
示例：
1. openGauss=# select jsonb_contains('[1,2,3,4]', '[1,2,3]');
2. jsonb_contains
3. ----------------
4. t
5. (1 row)
- jsonb_exists(jsonb, text)
描述：同操作符 ?, 字符串$2是否存在$1的顶层以key\elem\scalar的形式存在。
返回类型：bool
示例：
1. openGauss=# select jsonb_exists('["1",2,3]', '1');
2. jsonb_exists
3. --------------
4. t
5. (1 row)
- jsonb_exists_all(jsonb, text[])
描述：同操作符 ?&, 字符串数组$2里面，是否所有的元素，都在$1的顶层以key\elem\scalar的形式存在。
返回类型：bool
示例：
1. openGauss=# select jsonb_exists_all('["1","2",3]', '{1, 2}');
2. jsonb_exists_all
3. ------------------
4. t
5. (1 row)
- jsonb_exists_any(jsonb, text[])
描述：同操作符 ?|, 字符串数组$2里面，是否存在的元素，在$1的顶层以key\elem\scalar的形式存在。
返回类型：bool
示例：
1. openGauss=# select jsonb_exists_any('["1","2",3]', '{1, 2, 4}');
2. jsonb_exists_any
3. ------------------
4. t
5. (1 row)
- - jsonb_cmp(jsonb, jsonb)
描述：比较大小，正数代表大于，负数代表小于，0表示相等。
返回类型：integer
示例：
1. openGauss=# select jsonb_cmp('["a", "b"]', '{"a":1, "b":2}');
2. jsonb_cmp
3. -----------
4. -1
5. (1 row)
- jsonb_eq(jsonb, jsonb)
描述：同操作符 =, 比较两个值的大小。
返回类型：bool
示例：
1. openGauss=# select jsonb_eq('["a", "b"]', '{"a":1, "b":2}');
2. jsonb_eq
3. ----------
4. f
5. (1 row)
- - jsonb_ne(jsonb, jsonb)
描述：同操作符 <>, 比较两个值的大小。
返回类型：bool
示例：
1. openGauss=# select jsonb_ne('["a", "b"]', '{"a":1, "b":2}');
2. jsonb_ne
3. ----------
4. t
5. (1 row)
- jsonb_gt(jsonb, jsonb)
描述：同操作符 >, 比较两个值的大小。
返回类型：bool
示例：
1. openGauss=# select jsonb_gt('["a", "b"]', '{"a":1, "b":2}');
2. jsonb_gt
3. ----------
4. f
5. (1 row)
- jsonb_ge(jsonb, jsonb)
描述：同操作符 >=, 比较两个值的大小。
返回类型：bool
示例：
1. openGauss=# select jsonb_ge('["a", "b"]', '{"a":1, "b":2}');
2. jsonb_ge
3. ----------
4. f
5. (1 row)
- jsonb_lt(jsonb, jsonb)
描述：同操作符 <, 比较两个值的大小。
返回类型：bool
示例：
1. openGauss=# select jsonb_lt('["a", "b"]', '{"a":1, "b":2}');
2. jsonb_lt
3. ----------
4. t
5. (1 row)
- jsonb_le(jsonb, jsonb)
描述：同操作符 <=, 比较两个值的大小。
返回类型：bool
示例：
1. openGauss=# select jsonb_le('["a", "b"]', '{"a":1, "b":2}');
2. jsonb_le
3. ----------
4. t
5. (1 row)
- to_json(anyelement)
描述：把参数转换为json。
返回类型：json
示例：
1. openGauss=# select to_json('{1,5}'::text[]);
2. to_json
3. -----------
4. ["1","5"]
5. (1 row)
- jsonb_hash(jsonb)
描述：对jsonb进行hash运算。
返回类型：integer
示例：
1. openGauss=# select jsonb_hash('[1,2,3]');
2. jsonb_hash
3. ------------
4. -559968547
5. (1 row)
- 其他函数
描述：gin索引以及json\jsonb聚集函数所用到的内部函数，功能不过多赘述。
``` 1. gin_compare_jsonb 2. gin_consistent_jsonb 3. gin_consistent_jsonb_hash 4. gin_extract_jsonb 5. gin_extract_jsonb_hash 6. gin_extract_jsonb_query 7. gin_extract_jsonb_query_hash 8. gin_triconsistent_jsonb 9. gin_triconsistent_jsonb_hash
- json_agg_transfn
- json_agg_finalfn
- json_object_agg_transfn
- json_object_agg_finalfn ```
