PostgreSQL 9.3beta1 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 builtin, 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 NULL, 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. A column may only be specified once.

`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. A column may only be specified once.

`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 hstore extension has a cast from hstore to json, so that converted hstore values are represented as json objects, not as string values.

See also Section 9.20 about the aggregate function `json_agg` which aggregates record values as json efficiently.

Prev | Home | Next
XML Functions | Up | Sequence Manipulation Functions