JSON functions and operators
更新时间：
复制 MD 格式
AnalyticDB for PostgreSQL supports the JSON functions and operators from PostgreSQL. Each entry below includes the full signature, a description of its behavior, and a working example.
For the full PostgreSQL reference, see JSON Functions and Operators.
JSON and JSONB operators
AnalyticDB for PostgreSQL stores JSON data in two types: JSON and JSONB. Operators on these types let you extract values, test containment, and modify JSONB documents.
Operators supported by JSON and JSONB
Each operator below works on both json and jsonb inputs. The return type matches the input type, except where the return type is listed as text.
| Operator signature | Description | Example | Result |
|---|---|---|---|
json -> integer → json/jsonb | Returns the JSON array element at the given index (0-based). Negative integers count from the end. | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"} |
json -> text → json/jsonb | Returns the JSON object field by key. | '{"a": {"b":"foo"}}'::json->'a' | {"b":"foo"} |
json ->> integer → text | Returns the JSON array element at the given index as text. | '[1,2,3]'::json->>2 | 3 |
json ->> text → text | Returns the JSON object field by key as text. | '{"a":1,"b":2}'::json->>'b' | 2 |
json #> text[] → json/jsonb | Returns the JSON value at the specified path. | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"} |
json #>> text[] → text | Returns the JSON value at the specified path as text. | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' | 3 |
Operators supported only by JSONB
These operators apply only to the jsonb type.
| Operator signature | Description | Example |
|---|---|---|
jsonb @> jsonb → boolean | Checks whether the left JSONB value contains the right JSON path or value at the top level. | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
jsonb <@ jsonb → boolean | Checks whether the left JSON path or value is contained within the right JSONB value at the top level. | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
jsonb ? text → boolean | Checks whether the given string exists as a top-level key or array element. | '{"a":1, "b":2}'::jsonb ? 'b' |
jsonb ?| text[] → boolean | Checks whether any string in the array exists as a top-level key. | '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
jsonb ?& text[] → boolean | Checks whether all strings in the array exist as top-level keys. | '["a", "b"]'::jsonb ?& array['a', 'b'] |
jsonb || jsonb → jsonb | Concatenates two JSONB values. | '["a", "b"]'::jsonb || '["c", "d"]'::jsonb |
jsonb - text → jsonb | Deletes the key-value pair matching the given key. | '{"a": "b"}'::jsonb - 'a' |
jsonb - text[] → jsonb | Deletes all key-value pairs matching the given keys. | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] |
jsonb - integer → jsonb | Deletes the array element at the given index. Negative integers count from the end. Raises an error if the top-level container is not an array. | '["a", "b"]'::jsonb - 1 |
jsonb #- text[] → jsonb | Deletes the field or element at the given path. Negative integers count from the end. | '["a", {"b":1}]'::jsonb #- '{1,b}' |
jsonb @? jsonpath → boolean | Checks whether any JSON elements at the given path match a condition. | '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' |
jsonb @@ jsonpath → boolean | Returns the result of the first element at the given path. Returns null if the result is not a Boolean. | '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' |
JSON functions
AnalyticDB for PostgreSQL provides two categories of JSON functions: creation functions that build JSON values from SQL data, and processing functions that query or transform JSON values.
JSON creation functions
Important
The JSONB variants to_jsonb, jsonb_build_array, jsonb_build_object, and jsonb_object are supported only on AnalyticDB for PostgreSQL V7.0 instances.
| Function | Description | Example | Result |
|---|---|---|---|
to_json(value anyelement) to_jsonb(value anyelement) | Returns the JSON or JSONB representation of the value. Arrays and sets become JSON arrays. If a cast to json exists for a non-array type, it is used; otherwise the scalar is returned as-is. Non-numeric, non-boolean, non-null scalars are represented as their text form. | to_json('Fred said "Hi."'::text) | "Fred said \"Hi.\"" |
array_to_json(array anyarray [, pretty_bool boolean]) | Returns the array as a JSON array. Multidimensional arrays become JSON arrays of arrays. When pretty_bool is true, line feeds are added between dimension-1 elements. | array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
row_to_json(record record [, pretty_bool boolean]) | Returns the row as a JSON object. When pretty_bool is true, line feeds are added between top-level elements. | row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
json_build_array(VARIADIC "any") jsonb_build_array(VARIADIC "any") | Builds a JSON array from a variable argument list. Arguments may be of different types. | 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 from a variable argument list. Arguments must be 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 from a text array. Pass a flat 1-D array with an even number of elements (alternating keys and values), or a 2-D array where each inner array has exactly two elements. | 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[]) | Builds a JSON object from separate key and value arrays. Otherwise behaves the same as the single-argument form. | json_object('{a, b}', '{1,2}') | {"a": "1", "b": "2"} |
JSON processing functions
| Function | Return type | Description | 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 record (key text, value json/jsonb) | Expands the outermost JSON object into a set of key-value pairs. | SELECT * FROM json_each('{"a":"foo", "b":"bar"}') | key | value<br>-----+-------<br>a | "foo"<br>b | "bar" |
json_each_text(json) jsonb_each_text(jsonb) | setof record (key text, value text) | Expands the outermost JSON object into key-value pairs. Values are returned as text. | SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}') | key | value<br>-----+-------<br>a | foo<br>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 the JSON value at the given path. Equivalent to the #> 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 the JSON value at the given path as text. Equivalent to the #>> 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 the set of keys in the outermost JSON object. | json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') | json_object_keys<br>------------------<br>f1<br>f2 |
json_populate_record(base anyelement, from_json json) jsonb_populate_record(base anyelement, from_json jsonb) | anyelement | Expands the JSON object into a row whose columns match the record type of base. | SELECT * FROM json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}') | a | b | c<br>---+-----------+-------------<br>1 | {2,"a b"} | (4,"a b c") |
json_populate_recordset(base anyelement, from_json json) jsonb_populate_recordset(base anyelement, from_json jsonb) | setof anyelement | Expands the outermost JSON array of objects into a set of rows matching the record type of base. | SELECT * FROM json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]') | a | b<br>---+---<br>1 | 2<br>3 | 4 |
json_array_elements(json) jsonb_array_elements(jsonb) | setof json/jsonb | Expands a JSON array into a set of JSON values. | SELECT * FROM json_array_elements('[1,true, [2,false]]') | value<br>-----------<br>1<br>true<br>[2,false] |
json_array_elements_text(json) jsonb_array_elements_text(jsonb) | setof text | Expands a JSON array into a set of text values. | SELECT * FROM json_array_elements_text('["foo", "bar"]') | value<br>-------<br>foo<br>bar |
json_typeof(json) jsonb_typeof(jsonb) | text | Returns the type of the outermost JSON value as a text string: OBJECT, ARRAY, STRING, NUMBER, BOOLEAN, or null. | json_typeof('-123.4') | number |
json_to_record(json) jsonb_to_record(jsonb) | record | Builds a record from a JSON object. You must define the output structure with an AS clause. | SELECT * FROM json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, c text) | a | b | c<br>---+---------+-----<br>1 | [1,2,3] | bar |
json_to_recordset(json) jsonb_to_recordset(jsonb) | setof record | Builds a set of records from a JSON array of objects. You must define the output structure 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<br>---+-----<br>1 | foo<br>2 | |
json_strip_nulls(from_json json) jsonb_strip_nulls(from_json jsonb) | json/jsonb | Returns the input with all object fields that have null values removed. Null values that are not object fields (for example, null elements in arrays) are preserved. | 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 | Replaces the value at the given path with new_value. If the path does not exist and create_missing is true (the default), the new value is inserted. Negative integers in the 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 | Inserts new_value at the given path. For array targets, new_value is inserted before the target element by default; set insert_after to true to insert after. For object targets where the key does not exist, the value is inserted unconditionally. Negative integers in the path count from the end. | 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 the JSONB value as indented, human-readable JSON text. | jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') | [<br> {<br> "f1": 1,<br> "f2": null<br> },<br> 2,<br> null,<br> 3<br>] |
jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]]) | boolean | Checks whether any JSON elements at the given path match a condition. | jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') | true |
jsonb_path_match(target jsonb, path jsonpath [, vars jsonb [, silent bool]]) | boolean | Returns the Boolean result of the first element at the given path. Returns null if the result is not Boolean. | jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2,"max":4}') | true |
jsonb_path_query(target jsonb, path jsonpath [, vars jsonb [, silent bool]]) | setof jsonb | Returns all JSON items at the given path as a set. | SELECT * FROM jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') | jsonb_path_query<br>------------------<br>2<br>3<br>4 |
jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb [, silent bool]]) | jsonb | Returns all JSON items at the given path wrapped in a JSON array. | jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') | [2, 3, 4] |
jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb [, silent bool]]) | jsonb | Returns the first JSON item at the given path. Returns NULL if no match is found. | jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') | 2 |
该文章对您有帮助吗？