| PostgreSQL 9.1devel Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Appendix F. Additional Supplied Modules | Fast Forward | Next |
This module implements the json data type for storing JSON content in PostgreSQL. The advantage of using the json type over storing JSON content in a text field is that it makes sure input values are valid JSON, and there are several type-safe functions for manipulating JSON content.
The json type stores valid JSON "values" as defined by json.org. That is, a json field can hold a string, number, object, array, 'true', 'false', or 'null'.
The json datatype should be thought of as a specialization of text rather than a wrapper around text, int, float, etc. For instance, ' "string" '::json::text will simply yield ' "string" '. Also, bear in mind that JSON null ('null'::json) and SQL NULL (NULL::json) are two different things.
The json module is currently under development.
Table F-11. json Functions
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
to_json(anyelement) | json | Encode a value as JSON. | to_json('string'::TEXT) | '"string"' |
| to_json(array['one','two','three',null]::text[]) | '["one","two","three",null]' | |||
from_json(json) | text | Decode a JSON-encoded value. | from_json('"string"') | 'string' |
json_validate(text) | boolean | Determine if text is valid JSON. | json_validate('{key: "value"}') | false |
| json_validate('{"key": "value"}') | true | |||
json_get(json, jsonpath text) | json | Select a single value from a JSON tree using a JSONPath expression. | json_get('[0,1,2]', '$[1]') | '1' |
| json_get('[0,1,2]', '$[100]') | NULL | |||
| json_get('[0,1,2]', '$[*]') | Error | |||
json_set(json, jsonpath text, json) | json | Set items in a JSON tree that match a JSONPath expression. | json_set('[0,1,2]', '$[1]', '"x"') | '[0,"x",2]' |
| json_set('[0,1,2]', '$[100]', '"x"') | '[0,1,2]' | |||
| json_set('[0,1,2]', '$[*]', '"x"') | '["x","x","x"]' | |||
json_path(json, jsonpath text) | setof json | Select multiple values from a JSON tree using a JSONPath expression. | json_path('[0,1,2]', '$[1]') | 1 (1 row) |
| json_path('[0,1,2]', '$[100]') | (0 rows) | |||
| json_path('[0,1,2]', '$[*]') | 0 1 2 (3 rows) | |||
json_condense(json) | json | Re-encodes JSON to form a string with minimal length (mainly removes whitespace). | json_condense(' { "key" : "value"} ') | '{"key":"value"}' |
| json_condense($$ "\u266B" $$) | '"♫"' -- if encoding supports Unicode | |||
json_type(json) | json_type_t - one of:
'null' 'string' 'number' 'bool' 'object' 'array' | Get the type of a json value. | json_type('{"pi": "3.14159", "e": "2.71828"}') | 'object' |
Joey Adams <joeyadams3.14159@gmail.com>
Development of this module was sponsored by Google through its Google Summer of Code program (code.google.com/soc).