Re: jsonpath

On Mon, Feb 26, 2018 at 6:34 PM, Nikita Glukhov <n.gluhov@postgrespro.ru> wrote:
> 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)

I think someday we should consider adding support for sql standard
conforming datetime. Since it
breaks postgres behaviour we will need 'standard_conforming_datetime' guc.


>
>
>    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)

Another note.
We decided to preserve TZ in JSON_QUERY function and follow standard
Postgres behaviour  in JSON_VALUE,
since JSON_QUERY returns JSON object and JSON_VALUE returns SQL value.

SELECT JSON_QUERY(jsonb '"2018-02-21 17:01:23 +05"',
'$.datetime("YYYY-MM-DD HH24:MI:SS TZH")');
         json_query
-----------------------------
 "2018-02-21T17:01:23+05:00"
(1 row)

show timezone;
 TimeZone
----------
 W-SU
(1 row)

SELECT JSON_VALUE(jsonb '"2018-02-21 17:01:23 +05"',
'$.datetime("YYYY-MM-DD HH24:MI:SS TZH")');
       json_value
------------------------
 2018-02-21 15:01:23+03
(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)
>

Documentation in user-friendly format (it will be convered to xml, of
course) is available
https://github.com/obartunov/sqljsondoc/blob/master/README.jsonpath.md
We are permanently working on it.



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