Re: jsonpath - Mailing list pgsql-hackers

Attached 10th version of the jsonpath patches.

1. Fixed error handling in arithmetic operators.

    Now run-time errors in arithmetic operators are catched (added
    PG_TRY/PG_CATCH around operator's functions calls) and converted into
    Unknown values in predicates as it is required by the standard:

    =# SELECT jsonb '[1,0,2]' @* '$[*] ? (1 / @ > 0)';
     ?column?
    ----------
     1
     2
    (2 rows)

2. Fixed grammar for parenthesized expressions.

3. Refactored GIN support for jsonpath operators.

4. Added one more operator json[b] @# jsonpath returning singleton json[b] with
    automatic conditional wrapping of sequences with more than one element into
    arrays:

    =# SELECT jsonb '[1,2,3,4,5]' @# '$[*] ? (@ > 2)';
     ?column?
    -----------
     [3, 4, 5]
    (1 row)

    =# SELECT jsonb '[1,2,3,4,5]' @# '$[*] ? (@ > 4)';
     ?column?
    ----------
     5
    (1 row)

    =# SELECT jsonb '[1,2,3,4,5]' @# '$[*] ? (@ > 5)';
     ?column?
    ----------
     (null)
    (1 row)

    Existing set-returning operator json[b] @* jsonpath is also very userful but
    can't be used in functional indices like new operator @#.

    Note that conditional wrapping of @# differs from the wrapping in
    JSON_QUERY(... WITH [ARRAY] WRAPPER), where only singleton objects and
    arrays are not wrapped.  Unconditional wrapping can be emulated with our
    array construction feature (see below).

5. Changed time zone behavior in .datetime() item method.

    In the previous version of the patch timestamptz SQL/JSON items were
    serialized into JSON string items using session time zone.  This behavior
    did not allow jsonpath operators to be marked as immutable, and therefore
    they could not be used in functional indices.  Also, when the time zone was
    not specified in the input string, but TZM or TZH format fields were present
    in the format string, session time zone was used as a default for
    timestamptz items.

    To make jsonpath operators immutable we decided to save input time zone for
    timestamptz items and disallow automatic time zone assignment.  Also
    additional parameter was added to .datetime() for default time zone
    specification:

    =# SET timezone = '+03';
    SET

    =# SELECT jsonb '"10-03-2017 12:34:56"' @*
                    '$.datetime("DD-MM-YYYY HH24:MI:SS TZH")';
    ERROR:  Invalid argument for SQL/JSON datetime function

    =# SELECT jsonb '"10-03-2017 12:34:56"' @*
                    '$.datetime("DD-MM-YYYY HH24:MI:SS TZH", "+05")';
              ?column?
    -----------------------------
     "2017-03-10T12:34:56+05:00"
    (1 row)

    =# SELECT jsonb '"10-03-2017 12:34:56 +05"' @*
                    '$.datetime("DD-MM-YYYY HH24:MI:SS TZH")';
              ?column?
    -----------------------------
     "2017-03-10T12:34:56+05:00"
    (1 row)

    Please note that our .datetime() behavior is not standard now: by the
    standard, input and format strings must match exactly, i.e. they both should
    not contain trailing unmatched elements, so automatic time zone assignment
    is impossible.  But it too restrictive for PostgreSQL users, so we decided
    to preserve usual PostgreSQL behavior here:

    =# SELECT jsonb '"10-03-2017"' @* '$.datetime("DD-MM-YYYY HH24:MI:SS")';
           ?column?
    -----------------------
     "2017-03-10T00:00:00"
    (1 row)


    Also PostgreSQL is able to automatically recognize format of the input
    string for the specified datetime type, but we can only bring this behavior
    into jsonpath by introducing separate item methods .date(), .time(),
    .timetz(), .timestamp() and .timestamptz().   Also we can use here our
    unfinished feature that gives us ability to work with PostresSQL types in
    jsonpath using cast operator :: (see sqljson_ext branch in our github repo):

    =# SELECT jsonb '"10/03/2017 12:34"' @* '$::timestamptz';
              ?column?
    -----------------------------
     "2017-03-10T12:34:00+03:00"
    (1 row)



A brief description of the extra jsonpath syntax features contained in the
patch #7:

   * Sequence construction by joining path expressions with comma:

     =# SELECT jsonb '[1, 2, 3]' @* '$[*], 4, 5';
      ?column?
     ----------
      1
      2
      3
      4
      5
     (5 rows)

   * Array construction by placing sequence into brackets (equivalent to
     JSON_QUERY(... WITH UNCONDITIONAL WRAPPER)):

     =# SELECT jsonb '[1, 2, 3]' @* '[$[*], 4, 5]';
         ?column?
     -----------------
      [1, 2, 3, 4, 5]
     (1 row)

   * Object construction by placing sequences of key-value pairs into braces:

     =# SELECT jsonb '{"a" : [1, 2, 3]}' @* '{a: [$.a[*], 4, 5], "b c": "dddd"}';
                    ?column?
     ---------------------------------------
      {"a": [1, 2, 3, 4, 5], "b c": "dddd"}
     (1 row)

   * Object subscripting with string-valued expressions:

     =# SELECT jsonb '{"a" : "aaa", "b": "a", "c": "ccc"}' @* '$[$.b, "c"]';
      ?column?
     ----------
      "aaa"
      "ccc"
     (2 rows)

   * Support of UNIX epoch time in .datetime() item method:

     =# SELECT jsonb '1519649957.37' @* '$.datetime()';
                 ?column?
     --------------------------------
      "2018-02-26T12:59:17.37+00:00"
     (1 row)

--
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company