Subject: Patch to add results for JSON operator examples

From: Sehrope Sarkuni <[email-masked]>
Date: Fri, 28 Mar 2014 15:51:47 -0400

The attached patches adds an "Example Result" column to the json/jsonb
operators table[1] that has the evaluated result of each example. I
think this makes it much easy to reason what each operator does. The
two patches are mutually exclusive and I recommend using the -better
patch.

The patches only touches the json and jsonb operators table, the
additional jsonb operators table is left without example results. All
the results for those examples are "true" so I don't think it's
needed. Maybe wouldn't hurt though.

[1]: http://www.postgresql.org/docs/devel/static/functions-json.html

The first file (docs-json-operator-example.diff) adds results for the
existing operator examples.

The other file (docs-json-operator-example-better.diff) replaces some
of the examples that return JSON objects so that the result is an
object (not a scalar). I like this one better.

For the operator examples that return json (as opposed to text), I
think the result should be a non-scalar. It makes it easier for
someone new reading the operator docs to understand which situations
they are used for.

For example, both of the following return "3" as a result but they
mean different things that are only apparent if you understand the
difference between the > and >> operators (ie look at the PG return
type).

=> SELECT x->'a'
->      , pg_typeof(x->'a')
->      , x->>'a'
->      , pg_typeof(x->>'a')
-> FROM (SELECT '{"a":3}'::json as x) t;
 ?column? | pg_typeof | ?column? | pg_typeof
----------+-----------+----------+-----------
 3        | json      | 3        | text
(1 row)

Whereas, the following clearly shows that it's returning an JSON
object with a PG type of json, not a scalar:

=> SELECT '{"a": {"b":"foo"}}'::json->'a';
  ?column?
-------------
 {"b":"foo"}
(1 row)

A related thought is changing the scalar results for the remaining
examples to something other than a single digit integer. Again, this
is to avoid confusion as someone new looking at it may not immediately
understand that it's actually a text string. Either that or showing it
in quotes so that it's apparent that it's text and not a number.

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | http://www.jackdb.com/
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 6e2fbda..14d1ac9
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** table2-mapping
*** 10093,10098 ****
--- 10093,10099 ----
          <entry>Right Operand Type</entry>
          <entry>Description</entry>
          <entry>Example</entry>
+         <entry>Example Result</entry>
         </row>
        </thead>
        <tbody>
*************** table2-mapping
*** 10101,10136 ****
--- 10102,10143 ----
          <entry>int</entry>
          <entry>Get JSON array element</entry>
          <entry><literal>'[1,2,3]'::json-&gt;2</literal></entry>
+         <entry><literal>3</literal></entry>
         </row>
         <row>
          <entry><literal>-&gt;</literal></entry>
          <entry>text</entry>
          <entry>Get JSON object field</entry>
          <entry><literal>'{"a":1,"b":2}'::json-&gt;'b'</literal></entry>
+         <entry><literal>2</literal></entry>
         </row>
          <row>
          <entry><literal>-&gt;&gt;</literal></entry>
          <entry>int</entry>
          <entry>Get JSON array element as text</entry>
          <entry><literal>'[1,2,3]'::json-&gt;&gt;2</literal></entry>
+         <entry><literal>3</literal></entry>
         </row>
         <row>
          <entry><literal>-&gt;&gt;</literal></entry>
          <entry>text</entry>
          <entry>Get JSON object field as text</entry>
          <entry><literal>'{"a":1,"b":2}'::json-&gt;&gt;'b'</literal></entry>
+         <entry><literal>2</literal></entry>
         </row>
         <row>
          <entry><literal>#&gt;</literal></entry>
          <entry>text[]</entry>
          <entry>Get JSON object at specified path</entry>
          <entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#&gt;'{a,2}'</literal></entry>
+         <entry><literal>3</literal></entry>
         </row>
         <row>
          <entry><literal>#&gt;&gt;</literal></entry>
          <entry>text[]</entry>
          <entry>Get JSON object at specified path as text</entry>
          <entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#&gt;&gt;'{a,2}'</literal></entry>
+         <entry><literal>3</literal></entry>
         </row>
        </tbody>
       </tgroup>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 6e2fbda..8a1a2e3
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** table2-mapping
*** 10093,10098 ****
--- 10093,10099 ----
          <entry>Right Operand Type</entry>
          <entry>Description</entry>
          <entry>Example</entry>
+         <entry>Example Result</entry>
         </row>
        </thead>
        <tbody>
*************** table2-mapping
*** 10100,10136 ****
          <entry><literal>-&gt;</literal></entry>
          <entry>int</entry>
          <entry>Get JSON array element</entry>
!         <entry><literal>'[1,2,3]'::json-&gt;2</literal></entry>
         </row>
         <row>
          <entry><literal>-&gt;</literal></entry>
          <entry>text</entry>
          <entry>Get JSON object field</entry>
!         <entry><literal>'{"a":1,"b":2}'::json-&gt;'b'</literal></entry>
         </row>
          <row>
          <entry><literal>-&gt;&gt;</literal></entry>
          <entry>int</entry>
          <entry>Get JSON array element as text</entry>
          <entry><literal>'[1,2,3]'::json-&gt;&gt;2</literal></entry>
         </row>
         <row>
          <entry><literal>-&gt;&gt;</literal></entry>
          <entry>text</entry>
          <entry>Get JSON object field as text</entry>
          <entry><literal>'{"a":1,"b":2}'::json-&gt;&gt;'b'</literal></entry>
         </row>
         <row>
          <entry><literal>#&gt;</literal></entry>
          <entry>text[]</entry>
!         <entry>Get JSON object at specified path</entry>
!         <entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#&gt;'{a,2}'</literal></entry>
         </row>
         <row>
          <entry><literal>#&gt;&gt;</literal></entry>
          <entry>text[]</entry>
          <entry>Get JSON object at specified path as text</entry>
          <entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#&gt;&gt;'{a,2}'</literal></entry>
         </row>
        </tbody>
       </tgroup>
--- 10101,10143 ----
          <entry><literal>-&gt;</literal></entry>
          <entry>int</entry>
          <entry>Get JSON array element</entry>
!         <entry><literal>'[{"a":"foo"},{"a":"bar"},{"a":"baz"}]'::json-&gt;2</literal></entry>
!         <entry><literal>{"a":"baz"}</literal></entry>
         </row>
         <row>
          <entry><literal>-&gt;</literal></entry>
          <entry>text</entry>
          <entry>Get JSON object field</entry>
!         <entry><literal>'{"a": {"b":"foo"}}'::json-&gt;'a'</literal></entry>
!         <entry><literal>{"b":"foo"}</literal></entry>
         </row>
          <row>
          <entry><literal>-&gt;&gt;</literal></entry>
          <entry>int</entry>
          <entry>Get JSON array element as text</entry>
          <entry><literal>'[1,2,3]'::json-&gt;&gt;2</literal></entry>
+         <entry><literal>3</literal></entry>
         </row>
         <row>
          <entry><literal>-&gt;&gt;</literal></entry>
          <entry>text</entry>
          <entry>Get JSON object field as text</entry>
          <entry><literal>'{"a":1,"b":2}'::json-&gt;&gt;'b'</literal></entry>
+         <entry><literal>2</literal></entry>
         </row>
         <row>
          <entry><literal>#&gt;</literal></entry>
          <entry>text[]</entry>
!         <entry>Get JSON object at specified path</entry>        
!         <entry><literal>'{"a": {"b":{"c": "foo"}}}'::json#&gt;'{a,b}'</literal></entry>
!         <entry><literal>{"c": "foo"}</literal></entry>
         </row>
         <row>
          <entry><literal>#&gt;&gt;</literal></entry>
          <entry>text[]</entry>
          <entry>Get JSON object at specified path as text</entry>
          <entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#&gt;&gt;'{a,2}'</literal></entry>
+         <entry><literal>3</literal></entry>
         </row>
        </tbody>
       </tgroup>

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 22 Apr 2014 18:19:32 -0400

> object (not a scalar). I like this one better.

"Better" patch version applied.  Thanks.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +