json

This module implements the json data type for storing JSON data in PostgreSQL. The advantage of using the json type over storing JSON data in a text field is that it performs JSON validation automatically, and there will be 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'. Be warned, though, that the JSON standard defines a top-level JSON text as an object or array, and many JSON libraries will only accept an object or array.

The json datatype is stored internally as JSON-formatted text and condensed, on input, to the smallest size possible[1]. For example, SELECT ' "json\u0020string" '::json; will yield '"json 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.

json Functions

FunctionReturn TypeDescriptionExampleResult
json_validate(text)booleanDetermine if text is valid JSON.json_validate('{key: "value"}')false
json_validate('{"key": "value"}')true
json_get_type(json)json_type -  one of: 
'null'
'string'
'number'
'bool'
'object'
'array'
Get the type of a json value.json_get_type('{"pi": "3.14159", "e": "2.71828"}')'object'
json_stringify(json)textConvert json to text. Currently, json_stringify(x) is equivalent to x::text. json_stringify('{"key":"value","array":[1,2,3]}'){"key":"value","array":[1,2,3]}
json_stringify(json, indent text)textConvert json to text, adding spaces and indentation for readability.json_stringify('{"a":true,"b":false,"array":[1,2,3]}', ' ')
{
    "a": true,
    "b": false,
    "array": [
        1,
        2,
        3
    ]
}

Author

Joey Adams

Development of this module was sponsored by Google through its Google Summer of Code program (code.google.com/soc).

Notes

[1]

If the server encoding is not UTF-8, escapes representing non-ASCII characters (e.g. "\u266B") are not converted to their respective characters (even when the server encoding has them) because it would introduce a performance penalty.