diff options
| -rw-r--r-- | doc/src/sgml/func.sgml | 216 | ||||
| -rw-r--r-- | doc/src/sgml/gin.sgml | 2 | ||||
| -rw-r--r-- | doc/src/sgml/json.sgml | 328 | ||||
| -rw-r--r-- | doc/src/sgml/release-9.3.sgml | 4 |
4 files changed, 255 insertions, 295 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 5126f14eb6a..8a10eb30cb2 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10081,7 +10081,7 @@ table2-mapping <indexterm zone="functions-json"> <primary>JSON</primary> - <secondary>Functions and operators</secondary> + <secondary>functions and operators</secondary> </indexterm> <para> @@ -10105,43 +10105,43 @@ table2-mapping <tbody> <row> <entry><literal>-></literal></entry> - <entry>int</entry> + <entry><type>int</type></entry> <entry>Get JSON array element</entry> <entry><literal>'[{"a":"foo"},{"a":"bar"},{"a":"baz"}]'::json->2</literal></entry> <entry><literal>{"a":"baz"}</literal></entry> </row> <row> <entry><literal>-></literal></entry> - <entry>text</entry> + <entry><type>text</type></entry> <entry>Get JSON object field</entry> <entry><literal>'{"a": {"b":"foo"}}'::json->'a'</literal></entry> <entry><literal>{"b":"foo"}</literal></entry> </row> <row> <entry><literal>->></literal></entry> - <entry>int</entry> - <entry>Get JSON array element as text</entry> + <entry><type>int</type></entry> + <entry>Get JSON array element as <type>text</></entry> <entry><literal>'[1,2,3]'::json->>2</literal></entry> <entry><literal>3</literal></entry> </row> <row> <entry><literal>->></literal></entry> - <entry>text</entry> - <entry>Get JSON object field as text</entry> + <entry><type>text</type></entry> + <entry>Get JSON object field as <type>text</></entry> <entry><literal>'{"a":1,"b":2}'::json->>'b'</literal></entry> <entry><literal>2</literal></entry> </row> <row> <entry><literal>#></literal></entry> - <entry>text[]</entry> + <entry><type>text[]</type></entry> <entry>Get JSON object at specified path</entry> <entry><literal>'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'</literal></entry> <entry><literal>{"c": "foo"}</literal></entry> </row> <row> <entry><literal>#>></literal></entry> - <entry>text[]</entry> - <entry>Get JSON object at specified path as text</entry> + <entry><type>text[]</type></entry> + <entry>Get JSON object at specified path as <type>text</></entry> <entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'</literal></entry> <entry><literal>3</literal></entry> </row> @@ -10152,14 +10152,17 @@ table2-mapping <note> <para> There are parallel variants of these operators for both the - <type>json</type> and <type>jsonb</type> types. In addition to - those operators common to both types, a further set of operators - exists for <type>jsonb</type> (which comprise the default - <acronym>GIN</acronym> operator class). + <type>json</type> and <type>jsonb</type> types. The operators + return the same type as their left-hand input (either <type>json</type> + or <type>jsonb</type>), except for those specified as + returning <type>text</>, which coerce the value to text. </para> </note> <para> - The following are <type>jsonb</>-only operators, used by + In addition to those operators common to both types, some additional + operators exist only for <type>jsonb</type>, as shown + in <xref linkend="functions-jsonb-op-table">. + Many of these operators can be indexed by <type>jsonb</> operator classes. For a full description of <type>jsonb</> containment semantics and nesting, see <xref linkend="json-containment">. <xref linkend="json-indexing"> @@ -10167,7 +10170,7 @@ table2-mapping <type>jsonb</>. </para> <table id="functions-jsonb-op-table"> - <title>Additonal JSONB Operators</title> + <title>Additional <type>jsonb</> Operators</title> <tgroup cols="4"> <thead> <row> @@ -10180,37 +10183,38 @@ table2-mapping <tbody> <row> <entry><literal>=</literal></entry> - <entry>jsonb</entry> - <entry>Is the jsonb equal to this jsonb?</entry> + <entry><type>jsonb</type></entry> + <entry>Are the two JSON values equal?</entry> <entry><literal>'[1,2,3]'::jsonb = '[1,2,3]'::jsonb</literal></entry> </row> <row> <entry><literal>@></literal></entry> - <entry>jsonb</entry> - <entry>Does the jsonb contain within it this jsonb?</entry> + <entry><type>jsonb</type></entry> + <entry>Does the left JSON value contain within it the right value?</entry> <entry><literal>'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb</literal></entry> </row> <row> <entry><literal><@</literal></entry> - <entry>jsonb</entry> - <entry>Does the jsonb have contained within it this jsonb?</entry> + <entry><type>jsonb</type></entry> + <entry>Is the left JSON value contained within the right value?</entry> <entry><literal>'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb</literal></entry> </row> <row> <entry><literal>?</literal></entry> - <entry>text</entry> - <entry>Does this key/element <emphasis>string</emphasis> exist?</entry> + <entry><type>text</type></entry> + <entry>Does the key/element <emphasis>string</emphasis> exist within + the JSON value?</entry> <entry><literal>'{"a":1, "b":2}'::jsonb ? 'b'</literal></entry> </row> <row> <entry><literal>?|</literal></entry> - <entry>text[]</entry> + <entry><type>text[]</type></entry> <entry>Do any of these key/element <emphasis>strings</emphasis> exist?</entry> <entry><literal>'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']</literal></entry> </row> <row> <entry><literal>?&</literal></entry> - <entry>text[]</entry> + <entry><type>text[]</type></entry> <entry>Do all of these key/element <emphasis>strings</emphasis> exist?</entry> <entry><literal>'["a", "b"]'::jsonb ?& array['a', 'b']</literal></entry> </row> @@ -10218,15 +10222,11 @@ table2-mapping </tgroup> </table> - <!-- - The release notes contain a reference to "functions-json-table". Since - that table is now split in two, the id has been parked here so we don't - have to change the release notes. - --> - <para id="functions-json-table"> + <para> <xref linkend="functions-json-creation-table"> shows the functions that are available for creating <type>json</type> values. - (see <xref linkend="datatype-json">) + (Currently, there are no equivalent functions for <type>jsonb</>, but you + can cast the result of one of these functions to <type>jsonb</>.) </para> <indexterm> @@ -10250,11 +10250,10 @@ table2-mapping <table id="functions-json-creation-table"> <title>JSON Creation Functions</title> - <tgroup cols="5"> + <tgroup cols="4"> <thead> <row> <entry>Function</entry> - <entry>Return Type</entry> <entry>Description</entry> <entry>Example</entry> <entry>Example Result</entry> @@ -10265,7 +10264,6 @@ table2-mapping <entry> <literal>array_to_json(anyarray [, pretty_bool])</literal> </entry> - <entry><type>json</type></entry> <entry> Returns the array as JSON. A PostgreSQL multidimensional array becomes a JSON array of arrays. Line feeds will be added between @@ -10278,7 +10276,6 @@ table2-mapping <entry> <literal>row_to_json(record [, pretty_bool])</literal> </entry> - <entry><type>json</type></entry> <entry> Returns the row as JSON. Line feeds will be added between level 1 elements if <parameter>pretty_bool</parameter> is true. @@ -10290,7 +10287,6 @@ table2-mapping <entry> <literal>to_json(anyelement)</literal> </entry> - <entry><type>json</type></entry> <entry> Returns the value as JSON. If the data type is not built in, and there is a cast from the type to <type>json</type>, the cast function will be used to @@ -10305,43 +10301,29 @@ table2-mapping <entry> <literal>json_build_array(VARIADIC "any")</literal> </entry> - <entry><type>json</type></entry> - <entry> - Builds a heterogeneously-typed json array out of a variadic argument list. - </entry> - <entry><literal>SELECT json_build_array(1,2,'3',4,5);</literal></entry> <entry> -<programlisting> - json_build_array -------------------- - [1, 2, "3", 4, 5] - </programlisting> + Builds a possibly-heterogeneously-typed JSON array out of a variadic + argument list. </entry> + <entry><literal>json_build_array(1,2,'3',4,5)</literal></entry> + <entry><literal>[1, 2, "3", 4, 5]</literal></entry> </row> <row> <entry> <literal>json_build_object(VARIADIC "any")</literal> </entry> - <entry><type>json</type></entry> - <entry> - Builds a JSON array out of a variadic argument list. By - convention, the object is constructed out of alternating - name/value arguments. - </entry> - <entry><literal>SELECT json_build_object('foo',1,'bar',2);</literal></entry> <entry> -<programlisting> - json_build_object ------------------------- - {"foo" : 1, "bar" : 2} - </programlisting> + Builds a JSON object out of a variadic argument list. By + convention, the argument list consists of alternating + names and values. </entry> + <entry><literal>json_build_object('foo',1,'bar',2)</literal></entry> + <entry><literal>{"foo" : 1, "bar" : 2}</literal></entry> </row> <row> <entry> <literal>json_object(text[])</literal> </entry> - <entry><type>json</type></entry> <entry> Builds a JSON object out of a text array. The array must have either exactly one dimension with an even number of members, in which case @@ -10349,42 +10331,28 @@ table2-mapping such that each inner array has exactly two elements, which are taken as a name/value pair. </entry> - <entry><literal>select * from json_object('{a, 1, b, "def", c, 3.5}') or <literal>select json_object('{{a, 1},{b, "def"},{c, 3.5}}')</literal></literal></entry> - <entry> -<programlisting> - json_object ---------------------------------------- - {"a" : "1", "b" : "def", "c" : "3.5"} - </programlisting> - </entry> + <entry><para><literal>json_object('{a, 1, b, "def", c, 3.5}')</></para> + <para><literal>json_object('{{a, 1},{b, "def"},{c, 3.5}}')</></para></entry> + <entry><literal>{"a" : "1", "b" : "def", "c" : "3.5"}</literal></entry> </row> <row> <entry> <literal>json_object(keys text[], values text[])</literal> </entry> - <entry><type>json</type></entry> <entry> - The two-argument form of JSON object takes keys and values pairwise from two separate + This form of <function>json_object</> takes keys and values pairwise from two separate arrays. In all other respects it is identical to the one-argument form. </entry> - <entry><literal>select json_object('{a, b}', '{1,2}');</literal></entry> - <entry> -<programlisting> - json_object ------------------------- - {"a" : "1", "b" : "2"} - </programlisting> - </entry> + <entry><literal>json_object('{a, b}', '{1,2}')</literal></entry> + <entry><literal>{"a" : "1", "b" : "2"}</literal></entry> </row> </tbody> </tgroup> </table> - <para> <xref linkend="functions-json-processing-table"> shows the functions that are available for processing <type>json</type> and <type>jsonb</type> values. - (see <xref linkend="datatype-json">) </para> <indexterm> @@ -10494,8 +10462,8 @@ table2-mapping <entry><para><literal>json_each(json)</literal> </para><para><literal>jsonb_each(jsonb)</literal> </para></entry> - <entry><para><literal>SETOF key text, value json</literal> - </para><para><literal>SETOF key text, value jsonb</literal> + <entry><para><literal>setof key text, value json</literal> + </para><para><literal>setof key text, value jsonb</literal> </para></entry> <entry> Expands the outermost JSON object into a set of key/value pairs. @@ -10514,10 +10482,10 @@ table2-mapping <entry><para><literal>json_each_text(from_json json)</literal> </para><para><literal>jsonb_each_text(from_json jsonb)</literal> </para></entry> - <entry><type>SETOF key text, value text</type></entry> + <entry><type>setof key text, value text</type></entry> <entry> Expands the outermost JSON object into a set of key/value pairs. The - returned value will be of type text. + returned value will be of type <type>text</>. </entry> <entry><literal>select * from json_each_text('{"a":"foo", "b":"bar"}')</literal></entry> <entry> @@ -10556,7 +10524,7 @@ table2-mapping <entry><para><literal>json_object_keys(json)</literal> </para><para><literal>jsonb_object_keys(jsonb)</literal> </para></entry> - <entry><type>SETOF text</type></entry> + <entry><type>setof text</type></entry> <entry> Returns set of keys in the JSON object. Only the <quote>outer</quote> object will be displayed. </entry> @@ -10595,7 +10563,7 @@ table2-mapping <entry><para><literal>json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false])</literal> </para><para><literal>jsonb_populate_recordset(base anyelement, from_json jsonb, [, use_json_as_text bool=false])</literal> </para></entry> - <entry><type>SETOF anyelement</type></entry> + <entry><type>setof anyelement</type></entry> <entry> Expands the outermost set of objects in <replaceable>from_json</replaceable> to a set whose columns match the record type defined by base. @@ -10618,13 +10586,13 @@ table2-mapping <entry><para><literal>json_array_elements(json)</literal> </para><para><literal>jsonb_array_elements(jsonb)</literal> </para></entry> - <entry><para><type>SETOF json</type> - </para><para><type>SETOF jsonb</type> + <entry><para><type>setof json</type> + </para><para><type>setof jsonb</type> </para></entry> <entry> Expands a JSON array to a set of JSON values. </entry> - <entry><literal>SELECT * FROM json_array_elements('[1,true, [2,false]]')</literal></entry> + <entry><literal>select * from json_array_elements('[1,true, [2,false]]')</literal></entry> <entry> <programlisting> value @@ -10639,11 +10607,11 @@ table2-mapping <entry><para><literal>json_array_elements_text(json)</literal> </para><para><literal>jsonb_array_elements_text(jsonb)</literal> </para></entry> - <entry><type>SETOF text</type></entry> + <entry><type>setof text</type></entry> <entry> - Expands a JSON array to a set of text values. + Expands a JSON array to a set of <type>text</> values. </entry> - <entry><literal>SELECT * FROM json_array_elements_text('["foo", "bar"]')</literal></entry> + <entry><literal>select * from json_array_elements_text('["foo", "bar"]')</literal></entry> <entry> <programlisting> value @@ -10674,9 +10642,9 @@ table2-mapping <entry><type>record</type></entry> <entry> Returns an arbitrary record from a JSON object. As with all functions - returning 'record', the caller must explicitly define the structure of the record + returning <type>record</>, the caller must explicitly define the structure of the record when making the call. The input JSON must be an object, not a scalar or an array. - If nested_as_text is true, the function coerces nested complex elements to text. + If <literal>nested_as_text</> is true, the function coerces nested complex elements to text. Also, see notes below on columns and types. </entry> <entry><literal>select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}',true) as x(a int, b text, d text) </literal></entry> @@ -10695,9 +10663,9 @@ table2-mapping <entry><type>setof record</type></entry> <entry> Returns an arbitrary set of records from a JSON object. As with - json_to_record, the structure of the record must be explicitly defined when making the - call. However, with json_to_recordset the input JSON must be an array containing - objects. nested_as_text works as with json_to_record. + <function>json_to_record</>, the structure of the record must be explicitly defined when making the + call. However, with <function>json_to_recordset</> the input JSON must be an array containing + objects. <literal>nested_as_text</> works as with <function>json_to_record</>. </entry> <entry><literal>select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]',true) as x(a int, b text);</literal></entry> <entry> @@ -10715,56 +10683,60 @@ table2-mapping <note> <para> - The <type>json</type> functions and operators can impose stricter validity requirements - than the type's input functions. In particular, they check much more closely that any use - of Unicode surrogate pairs to designate characters outside the Unicode Basic Multilingual - Plane is correct. + The <type>json</type> functions and operators can impose stricter + validity requirements than the JSON types' input functions do. In + particular, they check much more closely that any use of Unicode + surrogate pairs to designate characters outside the Unicode Basic + Multilingual Plane is correct. </para> </note> <note> <para> - Many of these functions and operators will convert Unicode escapes - in the JSON text to the appropriate UTF8 character when the database encoding is UTF8. In - other encodings the escape sequence must be for an ASCII character, and any other code point - in a Unicode escape sequence will result in an error. - In general, it is best to avoid mixing Unicode escapes in JSON with a non-UTF8 database - encoding, if possible. + Many of these functions and operators will convert Unicode escapes in + the JSON text to the appropriate UTF8 character when the database + encoding is UTF8. In other encodings the escape sequence must be for an + ASCII character, and any other code point in a Unicode escape sequence + will result in an error. In general, it is best to avoid mixing Unicode + escapes in JSON with a non-UTF8 database encoding, if possible. </para> </note> <note> <para> - In json_to_record and json_to_recordset, type-coercion from the JSON is - "best effort" and may not result in desired values for some types. JSON - elements are matched to identical field names in the record definition, - and elements which do not exist in the JSON will simply be NULL. JSON - elements which are not defined in the record template will - be omitted from the output. + In <function>json_to_record</> and <function>json_to_recordset</>, + type coercion from the JSON is <quote>best effort</> and may not result + in desired values for some types. JSON elements are matched to + identical field names in the record definition, and elements which do + not exist in the JSON will simply be NULL. JSON elements which are not + defined in the record template will be omitted from the output. </para> </note> <note> <para> - The <xref linkend="hstore"> extension has a cast from <type>hstore</type> to - <type>json</type>, so that converted <type>hstore</type> values are represented as JSON objects, + The <xref linkend="hstore"> extension has a cast + from <type>hstore</type> to <type>json</type>, so that + converted <type>hstore</type> values are represented as JSON objects, not as string values. </para> </note> <note> <para> - The <literal>json_typeof</> function's <literal>null</> return value should not be confused - with a SQL NULL. While calling <literal>json_typeof('null'::json)</> will return <literal>null</>, - calling <literal>json_typeof(NULL::json)</> will return a SQL NULL. + The <literal>json_typeof</> function's <literal>null</> return value + should not be confused with a SQL NULL. While + calling <literal>json_typeof('null'::json)</> will + return <literal>null</>, calling <literal>json_typeof(NULL::json)</> + will return a SQL NULL. </para> </note> <para> - See also <xref linkend="functions-aggregate"> about the aggregate + See also <xref linkend="functions-aggregate"> for the aggregate function <function>json_agg</function> which aggregates record - values as JSON efficiently, and the aggregate function - <function>json_object_agg</function>, which aggregates pairs of values + values as JSON, and the aggregate function + <function>json_object_agg</function> which aggregates pairs of values into a JSON object. </para> </sect1> diff --git a/doc/src/sgml/gin.sgml b/doc/src/sgml/gin.sgml index 576ad3005aa..41f7b913950 100644 --- a/doc/src/sgml/gin.sgml +++ b/doc/src/sgml/gin.sgml @@ -416,7 +416,7 @@ <para> Of the two operator classes for type <type>jsonb</>, <literal>jsonb_ops</> is the default. <literal>jsonb_hash_ops</> supports fewer operators but - will work with larger indexed values than <literal>jsonb_ops</> can support. + offers better performance for those operators. </para> </sect1> diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index 5fd24397429..592a5ce2b22 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -15,118 +15,148 @@ JSON data types are for storing JSON (JavaScript Object Notation) data, as specified in <ulink url="http://rfc7159.net/rfc7159">RFC 7159</ulink>. Such data can also be stored as <type>text</type>, but - both JSON data types have the advantage of enforcing that each - stored value is a valid JSON value. There are also related support - functions available; see <xref linkend="functions-json">. + the JSON data types have the advantage of enforcing that each + stored value is valid according to the JSON rules. There are also + assorted JSON-specific functions available for data stored in these + data types; see <xref linkend="functions-json">. </para> <para> There are two JSON data types: <type>json</> and <type>jsonb</>. - Both accept <emphasis>almost</emphasis> identical sets of values as + They accept <emphasis>almost</> identical sets of values as input. The major practical difference is one of efficiency. The <type>json</> data type stores an exact copy of the input text, - which processing functions must continually reparse, while + which processing functions must reparse on each execution; while <type>jsonb</> data is stored in a decomposed binary format that - makes it slightly less efficient to input due to added serialization + makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since it never needs - reparsing. <type>jsonb</> also supports advanced - <acronym>GIN</acronym> indexing, which is a further significant - advantage. + reparsing. <type>jsonb</> also supports indexing, which can be a + significant advantage. </para> <para> - The other difference between the types is that the <type>json</> - type is guaranteed to contain an exact copy of the input, including - preservation of semantically insignificant white space, and the - order of keys within JSON objects (although <type>jsonb</> will - preserve trailing zeros within a JSON number). Also, because the - exact text is kept, if a JSON object within the value contains the - same key more than once, and has been stored using the <type>json</> - type, all the key/value pairs are kept. In that case, the - processing functions consider the last value as the operative one. - By contrast, <type>jsonb</> does not preserve white space, does not - preserve the order of object keys, and does not keep duplicate - object keys. Only the last value for a key specified in the input - is kept. + Because the <type>json</> type stores an exact copy of the input text, it + will preserve semantically-insignificant white space between tokens, as + well as the order of keys within JSON objects. Also, if a JSON object + within the value contains the same key more than once, all the key/value + pairs are kept. (The processing functions consider the last value as the + operative one.) By contrast, <type>jsonb</> does not preserve white + space, does not preserve the order of object keys, and does not keep + duplicate object keys. Only the last value for a key specified in the + input is kept. <type>jsonb</> will preserve trailing zeros within a JSON + number, even though those are semantically insignificant for purposes such + as equality checks. </para> <para> - In general, most applications will prefer to store JSON data as - <type>jsonb</>, unless there are quite specialized needs. + In general, most applications should prefer to store JSON data as + <type>jsonb</>, unless there are quite specialized needs, such as + legacy assumptions about ordering of object keys. </para> <para> - <productname>PostgreSQL</productname> allows only one server + <productname>PostgreSQL</productname> allows only one character set encoding per database. It is therefore not possible for the JSON - types to conform rigidly to the specification unless the server + types to conform rigidly to the JSON specification unless the database encoding is UTF-8. Attempts to directly include characters which - cannot be represented in the server encoding will fail; conversely, - characters which can be represented in the server encoding but not + cannot be represented in the database encoding will fail; conversely, + characters which can be represented in the database encoding but not in UTF-8 will be allowed. <literal>\uXXXX</literal> escapes are - allowed regardless of the server encoding, and are checked only for + allowed regardless of the database encoding, and are checked only for syntactic correctness. </para> <sect2 id="json-types"> <title>Mapping of RFC-7159/JSON Primitive Types to <productname>PostgreSQL</productname> Types</title> <table id="json-type-mapping-table"> - <title>Mapping of type correspondence, notes</title> + <title>JSON scalar types and corresponding <productname>PostgreSQL</productname> types</title> <tgroup cols="3"> <thead> <row> - <entry><productname>PostgreSQL</productname> type</entry> <entry>RFC-7159/JSON primitive type</entry> + <entry><productname>PostgreSQL</productname> type</entry> <entry>Notes</entry> </row> </thead> <tbody> <row> - <entry><type>text</></entry> <entry><type>string</></entry> - <entry>See general introductory notes on encoding and JSON</entry> + <entry><type>text</></entry> + <entry>See introductory notes on JSON and encoding</entry> </row> <row> - <entry><type>numeric</></entry> <entry><type>number</></entry> + <entry><type>numeric</></entry> <entry><literal>NaN</literal> and <literal>infinity</literal> values are disallowed</entry> </row> <row> <entry><type>boolean</></entry> <entry><type>boolean</></entry> - <entry>Only lowercase <literal>true</literal> and <literal>false</literal> values are accepted</entry> + <entry>Only lowercase <literal>true</literal> and <literal>false</literal> spellings are accepted</entry> </row> <row> - <entry><type>unknown</></entry> <entry><type>null</></entry> - <entry>SQL <literal>NULL</literal> is orthogonal. NULL semantics do not apply.</entry> + <entry>(none)</entry> + <entry>SQL <literal>NULL</literal> is a different concept</entry> </row> </tbody> </tgroup> </table> <para> - Primitive types described by <acronym>RFC</> 7159 are effectively - internally mapped onto native - <productname>PostgreSQL</productname> types. Therefore, there are + When converting textual JSON input into <type>jsonb</>, + the primitive types described by <acronym>RFC</> 7159 are effectively + mapped onto native + <productname>PostgreSQL</productname> types, as shown in + <xref linkend="json-type-mapping-table">. Therefore, there are some very minor additional constraints on what constitutes valid <type>jsonb</type> that do not apply to the <type>json</type> - type, or to JSON in the abstract, that pertain to limits on what - can be represented by the underlying type system. These + type, nor to JSON in the abstract, corresponding to limits on what + can be represented by the underlying data type. Specifically, + <type>jsonb</> will reject numbers that are outside the range of + the <productname>PostgreSQL</productname> <type>numeric</> data type, + while <type>json</> will not. Such implementation-defined restrictions are permitted by - <acronym>RFC</> 7159. However, in practice problems are far more - likely to occur in other implementations which internally + <acronym>RFC</> 7159. However, in practice such problems are far more + likely to occur in other implementations, as it is common to represent the <type>number</> JSON primitive type as IEEE 754 - double precision floating point values, which <acronym>RFC</> 7159 - explicitly anticipates and allows for. When using JSON as an + double precision floating point (which <acronym>RFC</> 7159 + explicitly anticipates and allows for). When using JSON as an interchange format with such systems, the danger of losing numeric - precision in respect of data originally stored by + precision compared to data originally stored by <productname>PostgreSQL</productname> should be considered. </para> + <para> - Conversely, as noted above there are some minor restrictions on + Conversely, as noted in the table there are some minor restrictions on the input format of JSON primitive types that do not apply to - corresponding <productname>PostgreSQL</productname> types. + the corresponding <productname>PostgreSQL</productname> types. + </para> + </sect2> + + <sect2 id="json-keys-elements"> + <title><type>jsonb</> Input and Output Syntax</title> + <para> + The input/output syntax for the JSON data types is as specified in + <acronym>RFC</> 7159. </para> + <para> + The following are all valid <type>json</> (or <type>jsonb</>) expressions: + <programlisting> +-- Simple scalar/primitive value (explicitly required by RFC-7159) +SELECT '5'::json; +-- Array of heterogeneous, primitive-typed elements +SELECT '[1, 2, "foo", null]'::json; + +-- Object of heterogeneous key/value pairs of primitive types +-- Note that key values are always strings +SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json; + </programlisting> + </para> + <para> + Note the distinction between scalar/primitive values as array elements, + keys and values. + </para> </sect2> <sect2 id="json-querying"> @@ -144,46 +174,19 @@ summarize a set of <quote>documents</> (datums) in a table. </para> <para> - <type>jsonb</> data is subject to the same concurrency control + <type>json</> data is subject to the same concurrency control considerations as any other datatype when stored in a table. Although storing large documents is practicable, in order to ensure correct behavior row-level locks are, quite naturally, acquired as - rows are updated. Consider keeping <type>jsonb</> documents at a + rows are updated. Consider keeping <type>json</> documents at a manageable size in order to decrease lock contention among updating - transactions. Ideally, <type>jsonb</> documents should each + transactions. Ideally, <type>json</> documents should each represent an atomic datum that business rules dictate cannot reasonably be further subdivided into smaller atomic datums that can be independently modified. </para> </sect2> - <sect2 id="json-keys-elements"> - <title><type>jsonb</> Input and Output Syntax</title> - <para> - In effect, <type>jsonb</> has an internal type system whose - implementation is defined in terms of several particular ordinary - <productname>PostgreSQL</productname> types. The SQL parser does - not have direct knowledge of the internal types that constitute a - <type>jsonb</>. - </para> - <para> - The following are all valid <type>jsonb</> expressions: - <programlisting> --- Simple scalar/primitive value (explicitly required by RFC-7159) -SELECT '5'::jsonb; --- Array of heterogeneous, primitive-typed elements -SELECT '[1, 2, "foo", null]'::jsonb; - --- Object of heterogeneous key/value pairs of primitive types --- Note that key values are always strings -SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb; - </programlisting> - </para> - <para> - Note the distinction between scalar/primitive values as elements, - keys and values. - </para> - </sect2> <sect2 id="json-containment"> <title><type>jsonb</> containment</title> <indexterm> @@ -199,7 +202,7 @@ SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb; technically, top-down, unordered <emphasis>subtree isomorphism</> may be tested. Containment is conventionally tested using the <literal>@></> operator, which is made indexable by various - operator classes discussed later in this section. + operator classes discussed below. </para> <programlisting> -- Simple scalar/primitive values may contain only each other: @@ -249,45 +252,47 @@ SELECT '{"p":1, "a":{"b":3, "q":11}, "i":77}'::jsonb @> '{"a":{"b":3}}'::jsonb; </programlisting> <para> The various containment operators, along with all other JSON - operators and support functions are documented fully within <xref - linkend="functions-json">, <xref - linkend="functions-jsonb-op-table">. + operators and support functions are documented in <xref + linkend="functions-json">. </para> </sect2> + <sect2 id="json-indexing"> - <title><type>jsonb</> GIN Indexing</title> + <title><type>jsonb</> Indexing</title> <indexterm> <primary>jsonb</primary> <secondary>indexes on</secondary> </indexterm> + <para> - <type>jsonb</> GIN indexes can be used to efficiently search among - more than one possible key/value pair within a single - <type>jsonb</> datum/document, among a large number of such - documents within a column in a table (i.e. among many rows). + <type>jsonb</> GIN indexes can be used to efficiently search for + keys or key/value pairs occurring within a large number of + <type>jsonb</> documents (datums). + Two GIN <quote>operator classes</> are provided, offering different + performance and flexibility tradeoffs. </para> <para> - <type>jsonb</> has GIN index support for the <literal>@></>, - <literal>?</>, <literal>?&</> and <literal>?|</> operators. - The default GIN operator class makes all these operators - indexable: - </para> + The default GIN operator class supports queries with the + <literal>@></>, <literal>?</>, <literal>?&</> and <literal>?|</> + operators. + (For details of the semantics that these operators + implement, see <xref linkend="functions-jsonb-op-table">.) + An example of creating an index with this operator class is: <programlisting> --- GIN index (default opclass) -CREATE INDEX idxgin ON api USING GIN (jdoc); - --- GIN jsonb_hash_ops index -CREATE INDEX idxginh ON api USING GIN (jdoc jsonb_hash_ops); +CREATE INDEX idxgin ON api USING gin (jdoc); </programlisting> - <para> The non-default GIN operator class <literal>jsonb_hash_ops</> supports indexing the <literal>@></> operator only. + An example of creating an index with this operator class is: + <programlisting> +CREATE INDEX idxginh ON api USING gin (jdoc jsonb_hash_ops); + </programlisting> </para> + <para> Consider the example of a table that stores JSON documents retrieved from a third-party web service, with a documented schema - definition. An example of a document retrieved from this web - service is as follows: + definition. A typical document is: <programlisting> { "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a", @@ -305,85 +310,67 @@ CREATE INDEX idxginh ON api USING GIN (jdoc jsonb_hash_ops); ] } </programlisting> - If a GIN index is created on the table that stores these - documents, <literal>api</literal>, on its <literal>jdoc</> - <type>jsonb</> column, we can expect that queries like the - following may make use of the index: + We store these documents in a table named <structname>api</>, + in a <type>jsonb</> column named <structfield>jdoc</>. + If a GIN index is created on this column, + queries like the following can make use of the index: <programlisting> -- Note that both key and value have been specified -SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}'; +SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}'; </programlisting> However, the index could not be used for queries like the - following, due to the aforementioned nesting restriction: + following, because though the operator <literal>?</> is indexable, + it is not applied directly to the indexed column <structfield>jdoc</>: <programlisting> -SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui'; +SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui'; </programlisting> - Still, with judicious use of expressional indexing, the above + Still, with judicious use of expression indexes, the above query can use an index scan. If there is a requirement to find those records with a particular tag quickly, and the tags have a high cardinality across all documents, defining an index as follows is an effective approach to indexing: <programlisting> --- Note that the "jsonb -> text" operator can only be called on an --- object, so as a consequence of creating this index the root "jdoc" --- datum must be an object. This is enforced during insertion. -CREATE INDEX idxgin ON api USING GIN ((jdoc -> 'tags')); +-- Note that the "jsonb -> text" operator can only be called on an +-- object, so as a consequence of creating this index the root of each +-- "jdoc" value must be an object. This is enforced during insertion. +CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags')); </programlisting> + Now, the <literal>WHERE</> clause <literal>jdoc -> 'tags' ? 'qui'</> + will be recognized as an application of the indexable + operator <literal>?</> to the indexed + expression <literal>jdoc -> 'tags'</>. + (More information on expression indexes can be found in <xref + linkend="indexes-expressional">.) </para> <para> - Expressional indexes are discussed in <xref - linkend="indexes-expressional">. - </para> - <para> - For the most flexible approach in terms of what may be indexed, - sophisticated querying on nested structures is possible by - exploiting containment. At the cost of having to create an index - on the entire structure for each row, and not just a nested - subset, we may exploit containment semantics to get an equivalent - result with a non-expressional index on the entire <quote>jdoc</> - column, <emphasis>without</> ever having to create additional - expressional indexes against the document (provided only - containment will be tested). While the index will be considerably - larger than our expression index, it will also be much more - flexible, allowing arbitrary structured searching. Such an index - can generally be expected to help with a query like the following: - </para> + Another approach to querying is to exploit containment, for example: <programlisting> -SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}'; +SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}'; </programlisting> - <para> - For full details of the semantics that these indexable operators - implement, see <xref linkend="functions-json">, <xref - linkend="functions-jsonb-op-table">. - </para> - </sect2> - <sect2 id="json-opclass"> - <title><type>jsonb</> non-default GIN operator class</title> - <indexterm> - <primary>jsonb</primary> - <secondary>indexes on</secondary> - </indexterm> - <para> - Although only the <literal>@></> operator is made indexable, a - <literal>jsonb_hash_ops</literal> operator class GIN index has - some notable advantages over an equivalent GIN index of the - default GIN operator class for <type>jsonb</type>. Search - operations typically perform considerably better, and the on-disk - size of a <literal>jsonb_hash_ops</literal> operator class GIN - index can be much smaller. + This approach uses a single GIN index covering everything in the + <literal>jdoc</> column, whereas our expression index stored only + data found under the <literal>tags</> key. While the single-index + approach is certainly more flexible, targeted expression indexes + are likely to be smaller and faster to search than a single index. </para> - </sect2> - <sect2 id="json-btree-indexing"> - <title><type>jsonb</> B-Tree and hash indexing</title> + <para> - <type>jsonb</type> comparisons and related operations are - <emphasis>type-wise</>, in that the underlying - <productname>PostgreSQL</productname> datatype comparators are - invoked recursively, much like a traditional composite type. + Although the <literal>jsonb_hash_ops</literal> operator class supports + only queries with the <literal>@></> operator, it has notable + performance advantages over the default operator + class <literal>jsonb_ops</literal>. A <literal>jsonb_hash_ops</literal> + GIN index is usually much smaller than a <literal>jsonb_ops</literal> + index over the same data, and the specificity of searches is better, + particularly when queries contain tags that appear frequently in the + data. Therefore search operations typically perform considerably better + than with the default operator class. </para> + <para> - <type>jsonb</> also supports <type>btree</> and <type>hash</> - indexes. Ordering between <type>jsonb</> datums is: + <type>jsonb</> also supports <literal>btree</> and <literal>hash</> + indexes. These are usually useful only if it's important to check + equality of complete JSON documents. + The <literal>btree</> ordering for <type>jsonb</> datums is: <synopsis> <replaceable>Object</replaceable> > <replaceable>Array</replaceable> > <replaceable>Boolean</replaceable> > <replaceable>Number</replaceable> > <replaceable>String</replaceable> > <replaceable>Null</replaceable> @@ -391,23 +378,24 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}'; <replaceable>Array with n elements</replaceable> > <replaceable>array with n - 1 elements</replaceable> </synopsis> - Subsequently, individual primitive type comparators are invoked. - All comparisons of JSON primitive types occurs using the same - comparison rules as the underlying - <productname>PostgreSQL</productname> types. Strings are - compared lexically, using the default database collation. - Objects with equal numbers of pairs are compared: + Objects with equal numbers of pairs are compared in the order: <synopsis> <replaceable>key-1</replaceable>, <replaceable>value-1</replaceable>, <replaceable>key-2</replaceable> ... </synopsis> - Note however that object keys are compared in their storage order, and in particular, - since shorter keys are stored before longer keys, this can lead to results that might be - unintuitive, such as: - <programlisting>{ "aa": 1, "c": 1} > {"b": 1, "d": 1}</programlisting> + Note however that object keys are compared in their storage order, and + in particular, since shorter keys are stored before longer keys, this + can lead to results that might be unintuitive, such as: +<programlisting> +{ "aa": 1, "c": 1} > {"b": 1, "d": 1} +</programlisting> Similarly, arrays with equal numbers of elements are compared: <synopsis> <replaceable>element-1</replaceable>, <replaceable>element-2</replaceable> ... </synopsis> + Primitive JSON values are compared using the same + comparison rules as for the underlying + <productname>PostgreSQL</productname> data type. Strings are + compared using the default database collation. </para> </sect2> </sect1> diff --git a/doc/src/sgml/release-9.3.sgml b/doc/src/sgml/release-9.3.sgml index b4053c62bc8..64b1801f3c7 100644 --- a/doc/src/sgml/release-9.3.sgml +++ b/doc/src/sgml/release-9.3.sgml @@ -3618,14 +3618,14 @@ ALTER EXTENSION hstore UPDATE; <listitem> <para> Allow <type>JSON</> values to be <link - linkend="functions-json-table">converted into records</link> + linkend="functions-json">converted into records</link> (Andrew Dunstan) </para> </listitem> <listitem> <para> - Add <link linkend="functions-json-table">functions</link> to convert + Add <link linkend="functions-json">functions</link> to convert scalars, records, and <type>hstore</> values to <type>JSON</> (Andrew Dunstan) </para> |