Skip to content

Commit f825c7c

Browse files
tglsfdctglsfdc
committed
More work on the JSON/JSONB user documentation.
Document existence operator adequately; fix obsolete claim that no Unicode-escape semantic checks happen on input (it's still true for json, but not for jsonb); improve examples; assorted wordsmithing.
1 parent 866e6e1 commit f825c7c

File tree

3 files changed

+311
-219
lines changed

3 files changed

+311
-219
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 91 additions & 91 deletions
Original file line numberDiff line numberDiff line change
@@ -10106,14 +10106,14 @@ table2-mapping
1010610106
<row>
1010710107
<entry><literal>-&gt;</literal></entry>
1010810108
<entry><type>int</type></entry>
10109-
<entry>Get JSON array element</entry>
10110-
<entry><literal>'[{"a":"foo"},{"a":"bar"},{"a":"baz"}]'::json-&gt;2</literal></entry>
10111-
<entry><literal>{"a":"baz"}</literal></entry>
10109+
<entry>Get JSON array element (indexed from zero)</entry>
10110+
<entry><literal>'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json-&gt;2</literal></entry>
10111+
<entry><literal>{"c":"baz"}</literal></entry>
1011210112
</row>
1011310113
<row>
1011410114
<entry><literal>-&gt;</literal></entry>
1011510115
<entry><type>text</type></entry>
10116-
<entry>Get JSON object field</entry>
10116+
<entry>Get JSON object field by key</entry>
1011710117
<entry><literal>'{"a": {"b":"foo"}}'::json-&gt;'a'</literal></entry>
1011810118
<entry><literal>{"b":"foo"}</literal></entry>
1011910119
</row>
@@ -10134,7 +10134,7 @@ table2-mapping
1013410134
<row>
1013510135
<entry><literal>#&gt;</literal></entry>
1013610136
<entry><type>text[]</type></entry>
10137-
<entry>Get JSON object at specified path</entry>
10137+
<entry>Get JSON object at specified path</entry>
1013810138
<entry><literal>'{"a": {"b":{"c": "foo"}}}'::json#&gt;'{a,b}'</literal></entry>
1013910139
<entry><literal>{"c": "foo"}</literal></entry>
1014010140
</row>
@@ -10164,10 +10164,10 @@ table2-mapping
1016410164
in <xref linkend="functions-jsonb-op-table">.
1016510165
Many of these operators can be indexed by
1016610166
<type>jsonb</> operator classes. For a full description of
10167-
<type>jsonb</> containment semantics and nesting, see <xref
10167+
<type>jsonb</> containment and existence semantics, see <xref
1016810168
linkend="json-containment">. <xref linkend="json-indexing">
1016910169
describes how these operators can be used to effectively index
10170-
<type>jsonb</>.
10170+
<type>jsonb</> data.
1017110171
</para>
1017210172
<table id="functions-jsonb-op-table">
1017310173
<title>Additional <type>jsonb</> Operators</title>
@@ -10230,13 +10230,13 @@ table2-mapping
1023010230
</para>
1023110231

1023210232
<indexterm>
10233-
<primary>array_to_json</primary>
10233+
<primary>to_json</primary>
1023410234
</indexterm>
1023510235
<indexterm>
10236-
<primary>row_to_json</primary>
10236+
<primary>array_to_json</primary>
1023710237
</indexterm>
1023810238
<indexterm>
10239-
<primary>to_json</primary>
10239+
<primary>row_to_json</primary>
1024010240
</indexterm>
1024110241
<indexterm>
1024210242
<primary>json_build_array</primary>
@@ -10260,14 +10260,30 @@ table2-mapping
1026010260
</row>
1026110261
</thead>
1026210262
<tbody>
10263+
<row>
10264+
<entry>
10265+
<literal>to_json(anyelement)</literal>
10266+
</entry>
10267+
<entry>
10268+
Returns the value as JSON. Arrays and composites are converted
10269+
(recursively) to arrays and objects; otherwise, if there is a cast
10270+
from the type to <type>json</type>, the cast function will be used to
10271+
perform the conversion; otherwise, a JSON scalar value is produced.
10272+
For any scalar type other than a number, a boolean, or a null value,
10273+
the text representation will be used, properly quoted and escaped
10274+
so that it is a valid JSON string.
10275+
</entry>
10276+
<entry><literal>to_json('Fred said "Hi."'::text)</literal></entry>
10277+
<entry><literal>"Fred said \"Hi.\""</literal></entry>
10278+
</row>
1026310279
<row>
1026410280
<entry>
1026510281
<literal>array_to_json(anyarray [, pretty_bool])</literal>
1026610282
</entry>
1026710283
<entry>
10268-
Returns the array as JSON. A PostgreSQL multidimensional array
10284+
Returns the array as a JSON array. A PostgreSQL multidimensional array
1026910285
becomes a JSON array of arrays. Line feeds will be added between
10270-
dimension 1 elements if <parameter>pretty_bool</parameter> is true.
10286+
dimension-1 elements if <parameter>pretty_bool</parameter> is true.
1027110287
</entry>
1027210288
<entry><literal>array_to_json('{{1,5},{99,100}}'::int[])</literal></entry>
1027310289
<entry><literal>[[1,5],[99,100]]</literal></entry>
@@ -10277,26 +10293,12 @@ table2-mapping
1027710293
<literal>row_to_json(record [, pretty_bool])</literal>
1027810294
</entry>
1027910295
<entry>
10280-
Returns the row as JSON. Line feeds will be added between level
10281-
1 elements if <parameter>pretty_bool</parameter> is true.
10296+
Returns the row as a JSON object. Line feeds will be added between
10297+
level-1 elements if <parameter>pretty_bool</parameter> is true.
1028210298
</entry>
1028310299
<entry><literal>row_to_json(row(1,'foo'))</literal></entry>
1028410300
<entry><literal>{"f1":1,"f2":"foo"}</literal></entry>
1028510301
</row>
10286-
<row>
10287-
<entry>
10288-
<literal>to_json(anyelement)</literal>
10289-
</entry>
10290-
<entry>
10291-
Returns the value as JSON. If the data type is not built in, and there
10292-
is a cast from the type to <type>json</type>, the cast function will be used to
10293-
perform the conversion. Otherwise, for any value other than a number,
10294-
a Boolean, or a null value, the text representation will be used, escaped and
10295-
quoted so that it is legal JSON.
10296-
</entry>
10297-
<entry><literal>to_json('Fred said "Hi."'::text)</literal></entry>
10298-
<entry><literal>"Fred said \"Hi.\""</literal></entry>
10299-
</row>
1030010302
<row>
1030110303
<entry>
1030210304
<literal>json_build_array(VARIADIC "any")</literal>
@@ -10318,7 +10320,7 @@ table2-mapping
1031810320
names and values.
1031910321
</entry>
1032010322
<entry><literal>json_build_object('foo',1,'bar',2)</literal></entry>
10321-
<entry><literal>{"foo" : 1, "bar" : 2}</literal></entry>
10323+
<entry><literal>{"foo": 1, "bar": 2}</literal></entry>
1032210324
</row>
1032310325
<row>
1032410326
<entry>
@@ -10333,7 +10335,7 @@ table2-mapping
1033310335
</entry>
1033410336
<entry><para><literal>json_object('{a, 1, b, "def", c, 3.5}')</></para>
1033510337
<para><literal>json_object('{{a, 1},{b, "def"},{c, 3.5}}')</></para></entry>
10336-
<entry><literal>{"a" : "1", "b" : "def", "c" : "3.5"}</literal></entry>
10338+
<entry><literal>{"a": "1", "b": "def", "c": "3.5"}</literal></entry>
1033710339
</row>
1033810340
<row>
1033910341
<entry>
@@ -10344,12 +10346,30 @@ table2-mapping
1034410346
arrays. In all other respects it is identical to the one-argument form.
1034510347
</entry>
1034610348
<entry><literal>json_object('{a, b}', '{1,2}')</literal></entry>
10347-
<entry><literal>{"a" : "1", "b" : "2"}</literal></entry>
10349+
<entry><literal>{"a": "1", "b": "2"}</literal></entry>
1034810350
</row>
1034910351
</tbody>
1035010352
</tgroup>
1035110353
</table>
1035210354

10355+
<note>
10356+
<para>
10357+
<function>array_to_json</> and <function>row_to_json</> have the same
10358+
behavior as <function>to_json</> except for offering a pretty-printing
10359+
option. The behavior described for <function>to_json</> likewise applies
10360+
to each individual value converted by the other JSON creation functions.
10361+
</para>
10362+
</note>
10363+
10364+
<note>
10365+
<para>
10366+
The <xref linkend="hstore"> extension has a cast
10367+
from <type>hstore</type> to <type>json</type>, so that
10368+
<type>hstore</type> values converted via the JSON creation functions
10369+
will be represented as JSON objects, not as primitive string values.
10370+
</para>
10371+
</note>
10372+
1035310373
<para>
1035410374
<xref linkend="functions-json-processing-table"> shows the functions that
1035510375
are available for processing <type>json</type> and <type>jsonb</type> values.
@@ -10479,13 +10499,13 @@ table2-mapping
1047910499
</entry>
1048010500
</row>
1048110501
<row>
10482-
<entry><para><literal>json_each_text(from_json json)</literal>
10483-
</para><para><literal>jsonb_each_text(from_json jsonb)</literal>
10502+
<entry><para><literal>json_each_text(json)</literal>
10503+
</para><para><literal>jsonb_each_text(jsonb)</literal>
1048410504
</para></entry>
1048510505
<entry><type>setof key text, value text</type></entry>
1048610506
<entry>
1048710507
Expands the outermost JSON object into a set of key/value pairs. The
10488-
returned value will be of type <type>text</>.
10508+
returned values will be of type <type>text</>.
1048910509
</entry>
1049010510
<entry><literal>select * from json_each_text('{"a":"foo", "b":"bar"}')</literal></entry>
1049110511
<entry>
@@ -10504,7 +10524,7 @@ table2-mapping
1050410524
<entry><para><type>json</type></para><para><type>jsonb</type>
1050510525
</para></entry>
1050610526
<entry>
10507-
Returns JSON value pointed to by <parameter>path_elems</parameter>.
10527+
Returns JSON value pointed to by <replaceable>path_elems</replaceable>.
1050810528
</entry>
1050910529
<entry><literal>json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')</literal></entry>
1051010530
<entry><literal>{"f5":99,"f6":"foo"}</literal></entry>
@@ -10515,7 +10535,8 @@ table2-mapping
1051510535
</para></entry>
1051610536
<entry><type>text</type></entry>
1051710537
<entry>
10518-
Returns JSON value pointed to by <parameter>path_elems</parameter>.
10538+
Returns JSON value pointed to by <replaceable>path_elems</replaceable>
10539+
as <type>text</>.
1051910540
</entry>
1052010541
<entry><literal>json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')</literal></entry>
1052110542
<entry><literal>foo</literal></entry>
@@ -10526,7 +10547,7 @@ table2-mapping
1052610547
</para></entry>
1052710548
<entry><type>setof text</type></entry>
1052810549
<entry>
10529-
Returns set of keys in the JSON object. Only the <quote>outer</quote> object will be displayed.
10550+
Returns set of keys in the outermost JSON object.
1053010551
</entry>
1053110552
<entry><literal>json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')</literal></entry>
1053210553
<entry>
@@ -10544,13 +10565,11 @@ table2-mapping
1054410565
</para></entry>
1054510566
<entry><type>anyelement</type></entry>
1054610567
<entry>
10547-
Expands the object in <replaceable>from_json</replaceable> to a row whose columns match
10548-
the record type defined by base. Conversion will be best
10549-
effort; columns in base with no corresponding key in <replaceable>from_json</replaceable>
10550-
will be left null. When processing <type>json</type>, if a
10551-
column is specified more than once, the last value is used.
10568+
Expands the object in <replaceable>from_json</replaceable> to a row
10569+
whose columns match the record type defined by <replaceable>base</>
10570+
(see note below).
1055210571
</entry>
10553-
<entry><literal>select * from json_populate_record(null::x, '{"a":1,"b":2}')</literal></entry>
10572+
<entry><literal>select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}')</literal></entry>
1055410573
<entry>
1055510574
<programlisting>
1055610575
a | b
@@ -10565,14 +10584,12 @@ table2-mapping
1056510584
</para></entry>
1056610585
<entry><type>setof anyelement</type></entry>
1056710586
<entry>
10568-
Expands the outermost set of objects in <replaceable>from_json</replaceable> to a set
10569-
whose columns match the record type defined by base.
10570-
Conversion will be best effort; columns in base with no
10571-
corresponding key in <replaceable>from_json</replaceable> will be left null.
10572-
When processing <type>json</type>, if a column is specified more
10573-
than once, the last value is used.
10587+
Expands the outermost array of objects
10588+
in <replaceable>from_json</replaceable> to a set of rows whose
10589+
columns match the record type defined by <replaceable>base</> (see
10590+
note below).
1057410591
</entry>
10575-
<entry><literal>select * from json_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]')</literal></entry>
10592+
<entry><literal>select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')</literal></entry>
1057610593
<entry>
1057710594
<programlisting>
1057810595
a | b
@@ -10627,10 +10644,10 @@ table2-mapping
1062710644
</para></entry>
1062810645
<entry><type>text</type></entry>
1062910646
<entry>
10630-
Returns the type of the outermost JSON value as a text string. The types are
10647+
Returns the type of the outermost JSON value as a text string.
10648+
Possible types are
1063110649
<literal>object</>, <literal>array</>, <literal>string</>, <literal>number</>,
10632-
<literal>boolean</>, and <literal>null</>. (See note below regarding the
10633-
distinction between a JSON <literal>null</> and a SQL NULL.)
10650+
<literal>boolean</>, and <literal>null</>.
1063410651
</entry>
1063510652
<entry><literal>json_typeof('-123.4')</literal></entry>
1063610653
<entry><literal>number</literal></entry>
@@ -10641,11 +10658,11 @@ table2-mapping
1064110658
</para></entry>
1064210659
<entry><type>record</type></entry>
1064310660
<entry>
10644-
Returns an arbitrary record from a JSON object. As with all functions
10645-
returning <type>record</>, the caller must explicitly define the structure of the record
10646-
when making the call. The input JSON must be an object, not a scalar or an array.
10647-
If <literal>nested_as_text</> is true, the function coerces nested complex elements to text.
10648-
Also, see notes below on columns and types.
10661+
Builds an arbitrary record from a JSON object (see note below). As
10662+
with all functions returning <type>record</>, the caller must
10663+
explicitly define the structure of the record with an <literal>AS</>
10664+
clause. If <replaceable>nested_as_text</> is true, the function
10665+
coerces nested complex elements to text.
1064910666
</entry>
1065010667
<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>
1065110668
<entry>
@@ -10662,10 +10679,11 @@ table2-mapping
1066210679
</para></entry>
1066310680
<entry><type>setof record</type></entry>
1066410681
<entry>
10665-
Returns an arbitrary set of records from a JSON object. As with
10666-
<function>json_to_record</>, the structure of the record must be explicitly defined when making the
10667-
call. However, with <function>json_to_recordset</> the input JSON must be an array containing
10668-
objects. <literal>nested_as_text</> works as with <function>json_to_record</>.
10682+
Builds an arbitrary set of records from a JSON array of objects (see
10683+
note below). As with all functions returning <type>record</>, the
10684+
caller must explicitly define the structure of the record with
10685+
an <literal>AS</> clause. <replaceable>nested_as_text</> works as
10686+
with <function>json_to_record</>.
1066910687
</entry>
1067010688
<entry><literal>select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]',true) as x(a int, b text);</literal></entry>
1067110689
<entry>
@@ -10681,44 +10699,25 @@ table2-mapping
1068110699
</tgroup>
1068210700
</table>
1068310701

10684-
<note>
10685-
<para>
10686-
The <type>json</type> functions and operators can impose stricter
10687-
validity requirements than the JSON types' input functions do. In
10688-
particular, they check much more closely that any use of Unicode
10689-
surrogate pairs to designate characters outside the Unicode Basic
10690-
Multilingual Plane is correct.
10691-
</para>
10692-
</note>
10693-
1069410702
<note>
1069510703
<para>
1069610704
Many of these functions and operators will convert Unicode escapes in
10697-
the JSON text to the appropriate UTF8 character when the database
10698-
encoding is UTF8. In other encodings the escape sequence must be for an
10699-
ASCII character, and any other code point in a Unicode escape sequence
10700-
will result in an error. In general, it is best to avoid mixing Unicode
10701-
escapes in JSON with a non-UTF8 database encoding, if possible.
10705+
JSON strings to the appropriate single character. This is a non-issue
10706+
if the input is type <type>jsonb</>, because the conversion was already
10707+
done; but for <type>json</> input, this may result in throwing an error,
10708+
as noted in <xref linkend="datatype-json">.
1070210709
</para>
1070310710
</note>
1070410711

1070510712
<note>
1070610713
<para>
10707-
In <function>json_to_record</> and <function>json_to_recordset</>,
10714+
In <function>json_populate_record</>, <function>json_populate_recordset</>,
10715+
<function>json_to_record</> and <function>json_to_recordset</>,
1070810716
type coercion from the JSON is <quote>best effort</> and may not result
10709-
in desired values for some types. JSON elements are matched to
10710-
identical field names in the record definition, and elements which do
10711-
not exist in the JSON will simply be NULL. JSON elements which are not
10712-
defined in the record template will be omitted from the output.
10713-
</para>
10714-
</note>
10715-
10716-
<note>
10717-
<para>
10718-
The <xref linkend="hstore"> extension has a cast
10719-
from <type>hstore</type> to <type>json</type>, so that
10720-
converted <type>hstore</type> values are represented as JSON objects,
10721-
not as string values.
10717+
in desired values for some types. JSON keys are matched to
10718+
identical field names in the target row type, and fields that do
10719+
not exist in the JSON will simply be NULL. JSON keys that do not
10720+
appear in the target row type will be omitted from the output.
1072210721
</para>
1072310722
</note>
1072410723

@@ -10739,6 +10738,7 @@ table2-mapping
1073910738
<function>json_object_agg</function> which aggregates pairs of values
1074010739
into a JSON object.
1074110740
</para>
10741+
1074210742
</sect1>
1074310743

1074410744
<sect1 id="functions-sequence">

‎doc/src/sgml/gin.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -417,6 +417,7 @@
417417
Of the two operator classes for type <type>jsonb</>, <literal>jsonb_ops</>
418418
is the default. <literal>jsonb_hash_ops</> supports fewer operators but
419419
offers better performance for those operators.
420+
See <xref linkend="json-indexing"> for details.
420421
</para>
421422

422423
</sect1>

0 commit comments

Comments
 (0)