Not done yet but here's where I'm at. If I'm on the wrong track or
missing things that should be done please let me know.
[sqljson-dox-rework.patch]
Here are a few errors/typos/improvements.
I've added (=copied from the old docs) the CREATE TABLE for the my_films
table so that the more complicated json_table examples can be run easily.
Erik Rijkers
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
--- doc/src/sgml/func.sgml.orig 2022-05-14 06:32:28.564537299 +0200
+++ doc/src/sgml/func.sgml 2022-05-14 08:10:05.313313154 +0200
@@ -16287,7 +16287,7 @@
</para>
<para>
<literal>jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null)</literal>
- <returnvalue>[{"f1":null,"f2":null},2,null,3]</returnvalue>
+ <returnvalue>[{"f1": null, "f2": null}, 2, null, 3]</returnvalue>
</para>
<para>
<literal>jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target')</literal>
@@ -17682,7 +17682,7 @@
object keys.
</para>
<para>
- <literal>json('{"a" 123, "b":[true,"foo"], "a":"bar"}')</literal>
+ <literal>json('{"a":123, "b":[true,"foo"], "a":"bar"}')</literal>
<returnvalue>{"a":123, "b":[true,"foo"], "a":"bar"}</returnvalue>
</para>
<para>
@@ -17959,7 +17959,7 @@
</para>
<para>
<literal>json_exists(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR)</literal>
- <returnvalue>ERROR: Invalid SQL/JSON subscript</returnvalue>
+ <returnvalue>ERROR: jsonpath array subscript is out of bounds</returnvalue>
</para></entry>
</row>
<row>
@@ -17990,11 +17990,11 @@
of the <literal>ON EMPTY</literal> clause.
</para>
<para>
- <literal>json_value('"123.45"', '$' RETURNING float)</literal>
+ <literal>json_value('"123.45"'::jsonb, '$' RETURNING float)</literal>
<returnvalue>123.45</returnvalue>
</para>
<para>
- <literal>json_value('"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date)</literal>
+ <literal>json_value('"03:04 2015-02-01"'::jsonb, '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date)</literal>
<returnvalue>2015-02-01</returnvalue>
</para>
<para>
@@ -18111,7 +18111,7 @@
<listitem>
<para>
The input data to query, the JSON path expression defining the query,
- and an optional <literal>PASSING</literal> clause, which can privide data
+ and an optional <literal>PASSING</literal> clause, which can provide data
values to the <parameter>path_expression</parameter>.
The result of the input data
evaluation is called the <firstterm>row pattern</firstterm>. The row
@@ -18409,6 +18409,31 @@
<para>Examples</para>
<para>
+ In these examples the following small table storing some JSON data will be used:
+<programlisting>
+CREATE TABLE my_films ( js jsonb );
+
+INSERT INTO my_films VALUES (
+'{ "favorites" : [
+ { "kind" : "comedy", "films" : [
+ { "title" : "Bananas",
+ "director" : "Woody Allen"},
+ { "title" : "The Dinner Game",
+ "director" : "Francis Veber" } ] },
+ { "kind" : "horror", "films" : [
+ { "title" : "Psycho",
+ "director" : "Alfred Hitchcock" } ] },
+ { "kind" : "thriller", "films" : [
+ { "title" : "Vertigo",
+ "director" : "Alfred Hitchcock" } ] },
+ { "kind" : "drama", "films" : [
+ { "title" : "Yojimbo",
+ "director" : "Akira Kurosawa" } ] }
+ ] }');
+</programlisting>
+ </para>
+
+ <para>
Query the <structname>my_films</structname> table holding
some JSON data about the films and create a view that
distributes the film genre, title, and director between separate columns:
@@ -18427,7 +18452,7 @@
1 | comedy | Bananas | Woody Allen
1 | comedy | The Dinner Game | Francis Veber
2 | horror | Psycho | Alfred Hitchcock
- 3 | thriller | Vertigo | Hitchcock
+ 3 | thriller | Vertigo | Alfred Hitchcock
4 | drama | Yojimbo | Akira Kurosawa
(5 rows)
</screen>
