| PostgreSQL 9.3.25 Documentation | ||||
|---|---|---|---|---|
| Prev | Up | Chapter 9. Functionsand Operators | Next | |
Table9-40 shows the operators that are available for use with JSON(seeSection 8.14) data.
Table 9-40. JSON Operators
| Operator | Right Operand Type | Description | Example |
|---|---|---|---|
| -> | int | Get JSON array element | '[1,2,3]'::json->2 |
| -> | text | Get JSON object field | '{"a":1,"b":2}'::json->'b' |
| ->> | int | Get JSON array element as text | '[1,2,3]'::json->>2 |
| ->> | text | Get JSON object field as text | '{"a":1,"b":2}'::json->>'b' |
| #> | array of text | Get JSON object at specified path | '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}' |
| #>> | array of text | Get JSON object at specified path as text | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' |
Table9-41 shows the functions that are available for creating andmanipulating JSON (seeSection8.14) data.
Table 9-41. JSON Support Functions
| Function | Return Type | Description | Example | Example Result |
|---|---|---|---|---|
| array_to_json(anyarray [,pretty_bool]) | json | Returns the array as JSON. A PostgreSQL multidimensional arraybecomes a JSON array of arrays. Line feeds will be added betweendimension 1 elements ifpretty_bool istrue. | array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
| row_to_json(record [,pretty_bool]) | json | Returns the row as JSON. Line feeds will be added between level1 elements ifpretty_bool is true. | row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
| to_json(anyelement) | json | Returns the value as JSON. If the data type is not built in,and there is a cast from the type tojson,the cast function will be used to perform the conversion.Otherwise, for any value other than a number, a Boolean, or a nullvalue, the text representation will be used, escaped and quoted sothat it is legal JSON. | to_json('Fred said "Hi."'::text) | "Fred said \"Hi.\"" |
| json_array_length(json) | int | Returns the number of elements in the outermost JSONarray. | json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') | 5 |
| json_each(json) | SETOF key text, value json | Expands the outermost JSON object into a set of key/valuepairs. | select * from json_each('{"a":"foo","b":"bar"}') | key | value-----+------- a | "foo" b | "bar" |
| json_each_text(from_json json) | SETOF key text, value text | Expands the outermost JSON object into a set of key/valuepairs. The returned value 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, VARIADICpath_elems text[]) | json | Returns JSON object pointed to bypath_elems. | 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[]) | text | Returns JSON object pointed to bypath_elems. | json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4','f6') | foo |
| json_object_keys(json) | SETOF text | Returns set of keys in the JSON object. Only the"outer" object will be displayed. | json_object_keys('{"f1":"abc","f2":{"f3":"a","f4":"b"}}') | json_object_keys------------------ f1 f2 |
| json_populate_record(base anyelement,from_json json, [, use_json_as_text bool=false] | anyelement | Expands the object infrom_jsonto a row whose columns match the record type defined by base.Conversion will be best effort; columns in base with nocorresponding key infrom_json willbe left null. If a column is specified more than once, the lastvalue is used. | select * from json_populate_record(null::x,'{"a":1,"b":2}') | a | b---+--- 1 | 2 |
| json_populate_recordset(base anyelement,from_json json, [, use_json_as_text bool=false] | SETOF anyelement | Expands the outermost set of objects infrom_json to a set whose columns match therecord type defined by base. Conversion will be best effort;columns in base with no corresponding key infrom_json will be left null. If a column isspecified more than once, the last value is used. | select * fromjson_populate_recordset(null::x,'[{"a":1,"b":2},{"a":3,"b":4}]') | a | b---+--- 1 | 2 3 | 4 |
| json_array_elements(json) | SETOF json | Expands a JSON array to a set of JSON elements. | json_array_elements('[1,true,[2,false]]') | value----------- 1 true [2,false] |
Note: Thejson functions andoperators can impose stricter validity requirements than the type'sinput functions. In particular, they check much more closely thatany use of Unicode surrogate pairs to designate characters outsidethe Unicode Basic Multilingual Plane is correct.
Note: Many of these functions and operators will convertUnicode escapes in the JSON text to the appropriate UTF8 characterwhen the database encoding is UTF8. In other encodings the escapesequence must be for an ASCII character, and any other code pointin a Unicode escape sequence will result in an error. In general,it is best to avoid mixing Unicode escapes in JSON with a non-UTF8database encoding, if possible.
Note: Thehstore extension hasa cast fromhstore tojson, so that convertedhstorevalues are represented as JSON objects, not as string values.
See alsoSection 9.20about the aggregate functionjson_aggwhich aggregates record values as JSON efficiently.