Re: Patch: Improve Boolean Predicate JSON Path Docs - Mailing list pgsql-hackers

On Jan 19, 2024, at 21:46, Erik Wienhold <ewie@ewie.name> wrote:

> Interesting... copy-pasting the entire \set command works for me with
> psql 16.1 in gnome-terminal and tmux.  Typing it out manually gives me
> the "unterminated quoted string" error.  Maybe has to do with my stty
> settings.

Yes, same on macOS Terminal.app and 16.1 compiled with readline. I didn’t realize that \set didn’t support newlines,
becauseit works fine when you paste something with newlines. Curious. 

>> I experimented with
>>
>> SELECT '
>>  ... multiline json value ...
>> ' AS json
>> \gexec
>>
>> but that didn't seem to work either.  Anybody have a better idea?
>
> Fine with me (the \gset variant).

Much cleaner TBH.

david=# select '{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}'::jsonb as json;
                                                                                            json

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"track": {"segments": [{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135,
"location":[47.706, 13.2635], "start time": "2018-10-14 10:39:21"}]}} 
(1 row)

david=# \gset

david=# select :'json'::jsonb;
                                                                                           jsonb

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"track": {"segments": [{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135,
"location":[47.706, 13.2635], "start time": "2018-10-14 10:39:21"}]}} 
(1 row)

So great!

While you’re in there, Tom, would it make sense to fold in something like [this patch][1] I posted last month to
clarifywhich JSONPath comparison operators can take advantage of a index? 

--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -513,7 +513,7 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
</programlisting>
    For these operators, a GIN index extracts clauses of the form
    <literal><replaceable>accessors_chain</replaceable>
-    = <replaceable>constant</replaceable></literal> out of
+    == <replaceable>constant</replaceable></literal> out of
    the <type>jsonpath</type> pattern, and does the index search based on
    the keys and values mentioned in these clauses.  The accessors chain
    may include <literal>.<replaceable>key</replaceable></literal>,
@@ -522,6 +522,9 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
    The <literal>jsonb_ops</literal> operator class also
    supports <literal>.*</literal> and <literal>.**</literal> accessors,
    but the <literal>jsonb_path_ops</literal> operator class does not.
+    Only the <literal>==</literal> and <literal>!=</literal> <link
+    linkend="functions-sqljson-path-operators">SQL/JSON Path Operators</link>
+    can use the index.
  </para>

  <para>

Best,

David

  [1]: https://www.postgresql.org/message-id/0ECE6B9C-CDDE-4B65-BE5A-49D7372046AF@justatheory.com