1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
<sect1 id="json">
<title>json</title>
<indexterm zone="json">
<primary>json</primary>
</indexterm>
<para>
This module implements the <type>json</> data type for storing
<ulink url="http://www.json.org/">JSON</ulink> data in <productname>PostgreSQL</>.
The advantage of using the <type>json</> type over storing JSON data in a
<type>text</> field is that it performs JSON validation automatically, and
there will be several type-safe functions for manipulating JSON content.
</para>
<para>
The <type>json</> type stores valid JSON "values" as defined by
<ulink url="http://json.org/">json.org</ulink>. That is, a <type>json</>
field can hold a string, number, object, array, <literal>'true'</literal>,
<literal>'false'</literal>, or <literal>'null'</literal>. Be warned, though,
that the <ulink url="http://www.ietf.org/rfc/rfc4627.txt">JSON standard</ulink>
defines a top-level JSON text as an object or array, and many JSON libraries
will only accept an object or array.
</para>
<para>
The <type>json</> datatype is stored internally as JSON-formatted text and
condensed, on input, to the smallest size possible<footnote>
<simpara>If the server encoding is not UTF-8, escapes representing non-ASCII
characters (e.g. <literal>"\u266B"</literal>) are not converted to their
respective characters (even when the server encoding has them) because it
would introduce a performance penalty.</simpara>
</footnote>.
For example, <literal>SELECT ' "json\u0020string" '::json;</literal> will
yield <literal>'"json string"'</literal>. Also, bear in mind that JSON null
(<literal>'null'::json</literal>) and SQL NULL (<literal>NULL::json</literal>)
are two different things.
</para>
<para>
The json module is currently under development.
</para>
<sect2>
<title><type>json</> Functions</title>
<table id="json-func-table">
<title><type>json</type> Functions</title>
<tgroup cols="5">
<thead>
<row>
<entry>Function</entry>
<entry>Return Type</entry>
<entry>Description</entry>
<entry>Example</entry>
<entry>Result</entry>
</row>
</thead>
<tbody>
<row>
<entry morerows="1"><function>json_validate(text)</function></entry>
<entry morerows="1"><type>boolean</type></entry>
<entry morerows="1">Determine if text is valid JSON.</entry>
<entry><literal>json_validate('{key: "value"}')</literal></entry>
<entry><literal>false</literal></entry>
</row>
<row>
<entry><literal>json_validate('{"key": "value"}')</literal></entry>
<entry><literal>true</literal></entry>
</row>
<row>
<entry><function>json_get_type(json)</function></entry>
<entry><type>json_type</type> - one of:
<programlisting>
'null'
'string'
'number'
'bool'
'object'
'array'
</programlisting>
</entry>
<entry>Get the type of a <type>json</type> value.</entry>
<entry><literal>json_get_type('{"pi": "3.14159", "e": "2.71828"}')</literal></entry>
<entry><literal>'object'</literal></entry>
</row>
<row>
<entry><function>json_stringify(json)</function></entry>
<entry><type>text</type></entry>
<entry>Convert <type>json</type> to <type>text</type>. Currently,
<literal>json_stringify(x)</literal> is equivalent to
<literal>x::text</literal>.
</entry>
<entry><literal>json_stringify('{"key":"value","array":[1,2,3]}')</literal></entry>
<entry><literal>{"key":"value","array":[1,2,3]}</literal></entry>
</row>
<row>
<entry><function>json_stringify(json, indent text)</function></entry>
<entry><type>text</type></entry>
<entry>Convert <type>json</type> to <type>text</type>, adding spaces and indentation for readability.</entry>
<entry><literal>json_stringify('{"a":true,"b":false,"array":[1,2,3]}', ' ')</literal></entry>
<entry>
<programlisting>
{
"a": true,
"b": false,
"array": [
1,
2,
3
]
}
</programlisting>
</entry>
</row>
</tbody>
</tgroup>
</table>
</sect2>
<sect2>
<title>Author</title>
<para>
Joey Adams <email>joeyadams3.14159@gmail.com</email>
</para>
<para>
Development of this module was sponsored by Google through its Google Summer of Code program (<ulink url="http://code.google.com/soc">code.google.com/soc</ulink>).
</para>
</sect2>
</sect1>
|
