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]