PostgreSQL 9.3.4 Documentation Prev Up Chapter 9. Functions and Operators Next 9.15. JSON Functions and Operators

Table 9-39 shows the operators that are available for use with JSON (see Section 8.14) data.

Table 9-39. 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}'

Table 9-40 shows the functions that are available for creating and manipulating JSON (see Section 8.14) data.

Table 9-40. 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 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]) json Returns the row as JSON. Line feeds will be added between level 1 elements if pretty_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 to json, the cast function will be used to perform the conversion. Otherwise, for any value other than a number, a Boolean, or a null value, the text representation will be used, escaped and quoted so that 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 JSON array. 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/value pairs. 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/value pairs. 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, VARIADIC path_elems text[]) json Returns JSON object pointed to by path_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 by path_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 in from_json to a row whose columns match the record type defined by base. Conversion will be best effort; columns in base with no corresponding key in from_json will be left null. If a column is specified more than once, the last value 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 in from_json to a set whose columns match the record type defined by base. Conversion will be best effort; columns in base with no corresponding key in from_json will be left null. If a column is specified more than once, the last value is used. select * from json_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: The json functions and operators can impose stricter validity requirements than the type's input functions. In particular, they check much more closely that any use of Unicode surrogate pairs to designate characters outside the Unicode Basic Multilingual Plane is correct.