Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iXpND-0007Yw-VI for pgsql-hackers@arkaria.postgresql.org; Thu, 21 Nov 2019 16:32:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iXpNC-0008Fz-IO for pgsql-hackers@arkaria.postgresql.org; Thu, 21 Nov 2019 16:32:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iXpNC-0008F2-2O for pgsql-hackers@lists.postgresql.org; Thu, 21 Nov 2019 16:32:02 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iXpN8-0003w2-Qp for pgsql-hackers@postgresql.org; Thu, 21 Nov 2019 16:32:01 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id BDB0621C9E96; Thu, 21 Nov 2019 19:31:55 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [10.100.236.29] (unknown [195.82.154.53]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 5F81A21C9E86; Thu, 21 Nov 2019 19:31:55 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1574353915; bh=gDP3LMSZL9+p89FTYrxIW931PT/tP2ZVodnixbXLXME=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=ntcvHBrpWZmzhr1zu8WV4ATZ7DfeMXVix6noKxCyCnMg6J7JxhcWijQhk2482hiyH dZn2wqEb7bZ/rxTPrePW89su+HpaTwVo7wY3jdF/jXIHzHSk3Mpn6eoC8rzBTY6Yqa SrblvHkGYtg8E95QlcS5ovKosec9bE7VqQVnMtqY= Subject: Re: SQL/JSON: JSON_TABLE To: Pavel Stehule , PostgreSQL-development Cc: Robert Haas , Dmitry Dolgov <9erthalion6@gmail.com>, Oleg Bartunov , Andrew Dunstan References: <132f26c4-dfc6-f8fd-4764-2cbf455a3aec@postgrespro.ru> <7f23e8bd-d9fa-1c88-12ad-ded12ea5f221@postgrespro.ru> <94ab028a-07aa-38e5-b184-3d640f642bfb@postgrespro.ru> <5d304e31-9db2-c6d0-c282-b2e4000b0430@postgrespro.ru> From: Nikita Glukhov Organization: Postgres Professional Message-ID: <0350fdf8-c44d-46ab-6cc3-d39e77130cb1@postgrespro.ru> Date: Thu, 21 Nov 2019 19:28:00 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.9.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A8D0389F728CB3026BE27FC0" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A8D0389F728CB3026BE27FC0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 17.11.2019 13:35, Pavel Stehule wrote: > Hi > > út 12. 11. 2019 v 22:51 odesílatel Nikita Glukhov > > napsal: > > On 12.11.2019 20:54, Pavel Stehule wrote: > > > Hi > > > > please, can you rebase 0001-SQL-JSON-functions-v40.patch. I have a > > problem with patching > > > > Pavel > > Attached 41th version of the patches rebased onto current master. > > > I testing functionality - randomly testing some examples that I found > on internet. > Thank you for testing JSON_TABLE. > I found: > a) Oracle & MySQL (Oracle) supports EXISTS clause, this implementation not. > I think should be useful support this clause too. > SELECT * FROM JSON_TABLE('...', '...' COLUMNS x INT EXISTS PATH ... > EXISTS PATH clause can be emulated with jsonpath EXISTS() predicate: =# SELECT * FROM JSON_TABLE('{"a": 1}', '$' COLUMNS ( a bool PATH 'exists($.a)', b bool PATH 'exists($.b)' )); a | b ---+--- t | f (1 row) But this works as expected only in lax mode. In strict mode EXISTS() returns Unknown that transformed into SQL NULL: =# SELECT * FROM JSON_TABLE('{"a": 1}', '$' COLUMNS ( a bool PATH 'strict exists($.a)', b bool PATH 'strict exists($.b)' )); a | b ---+--- t | (1 row) There is no easy way to return false without external COALESCE(), DEFAULT false ON ERROR also does not help. So, I think it's worth to add EXISTS PATH clause to our implementation. > There is a question how to map boolean result to other data types. Now, boolean result can be used in JSON_TABLE columns of bool, int4, text, json[b], and other types which have CAST from bool: SELECT * FROM JSON_TABLE('{"a": 1}', '$' COLUMNS ( a int PATH 'exists($.a)', b text PATH 'exists($.b)' )); a | b ---+------- 1 | false (1 row) > b) When searched value is not scalar, then it returns null. This behave can be > suppressed by clause FORMAT Json. I found a different behave, and maybe I found > a bug. On MySQL this clause is by default for JSON values (what has sense). > SELECT * > FROM >       JSON_TABLE( >         '[{"a":[1,2]}]', >         '$[*]' >         COLUMNS( >          aj JSON PATH '$.a' DEFAULT '{"x": 333}' ON EMPTY >         ) >       ) AS tt; > It returns null, although it should to return [1,2]. Yes, regular (non-formatted) JSON_TABLE columns can accept only scalar values. Otherwise an error is thrown, which can be caught by ON ERROR clause. This behavior is specified by the standard. FORMAT JSON is not implicitly added for json[b] columns now. The current SQL standard does not have any json data types, so I think we can add implicit FORMAT JSON for json[b] typed-columns. But I'm a bit afraid that different behavior can be standardized after introduction of json data types in SQL. > There is another bug maybe. Although there is DEFAULT clause. It returns NULL. ON ERROR should be used if "not a scalar" error needs to be caught: SELECT * FROM JSON_TABLE( '[{"a":[1,2]}]', '$[*]' COLUMNS( aj JSON PATH '$.a' DEFAULT '{"x": 333}' ON ERROR ) ) AS tt; aj ------------ {"x": 333} (1 row) ON EMPTY catches only empty-result case (for example, non-existent path in lax mode): SELECT * FROM JSON_TABLE( '[{"a":[1,2]}]', '$[*]' COLUMNS( aj JSON PATH '$.foo' DEFAULT '{"x": 333}' ON EMPTY ) ) AS tt; aj ------------ {"x": 333} (1 row) > I got correct result when I used FORMAT JSON clause. > I think it should be default behave for json and jsonb columns. I agree that FORMAT JSON could be implicit for json[b] columns. But I think there could be one minor problem if we want to verify that returned value is scalar. Without FORMAT JSON this is verified by the underlying JSON_VALUE expression: SELECT * FROM JSON_TABLE( '[{"a":[1,2]}]', '$[*]' COLUMNS ( aj JSON PATH 'lax $.a' ERROR ON ERROR ) ) AS tt; ERROR: JSON path expression in JSON_VALUE should return singleton scalar item (This error message with the reference to implicit JSON_VALUE needs to be fixed.) But with FORMAT JSON we need to construct complex jsonpath with a filter and override ON EMPTY behavior: SELECT * FROM JSON_TABLE( '[{"a":[1,2]}]', '$[*]' COLUMNS ( aj JSON FORMAT JSON -- strict mode is mandatory to prevent array unwrapping PATH 'strict $.a ? (@.type() != "array" && @.type() != "object")' ERROR ON EMPTY ERROR ON ERROR ) ) AS tt; ERROR: no SQL/JSON item > Another question - when I used FORMAT JSON clause, then I got syntax error > on DEFAULT keyword .. . Is it correct? > > Why I cannot to use together FORMAT JSON and DEFAULT clauses? JSON_TABLE columns with FORMAT JSON, like JSON_QUERY, can have only ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT behaviors. This syntax is specified in the SQL standard: ::= FORMAT [ PATH ] [ WRAPPER ] [ QUOTES [ ON SCALAR STRING ] ] [ ON EMPTY ] [ ON ERROR ] ::= ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT ::= ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT But I also think that DEFAULT clause could be very useful in JSON_QUERY and formatted JSON_TABLE columns. > Note - this behave is not described in documentation. There are references to JSON_QUERY and JSON_VALUE behavior in the definitions of JSON_TABLE columns, but their behavior still seems to be unclear. This needs to be fixed. -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------A8D0389F728CB3026BE27FC0 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 17.11.2019 13:35, Pavel Stehule wrote:

Hi

út 12. 11. 2019 v 22:51 odesílatel Nikita Glukhov <n.gluhov@postgrespro.ru> napsal:
On 12.11.2019 20:54, Pavel Stehule wrote:

> Hi
>
> please, can you rebase 0001-SQL-JSON-functions-v40.patch. I have a
> problem with patching
>
> Pavel

Attached 41th version of the patches rebased onto current master.

I testing functionality - randomly testing some examples that I found on internet. 

Thank you for testing JSON_TABLE.
I found:

          

          

            
a) Oracle & MySQL (Oracle) supports EXISTS clause, this implementation not. 
I think should be useful support this clause too. 

          

          

            
SELECT * FROM JSON_TABLE('...', '...' COLUMNS x INT EXISTS PATH ...

          

          


          

        

      

    

    
EXISTS PATH clause can be emulated with jsonpath EXISTS() predicate:


    
=# SELECT * 
   FROM JSON_TABLE('{"a": 1}', '$'
                   COLUMNS (
                     a bool PATH 'exists($.a)',
                     b bool PATH 'exists($.b)'
                   ));
 a | b 
---+---
 t | f
(1 row)

But this works as expected only in lax mode.  In strict mode EXISTS() returns 
Unknown that transformed into SQL NULL:

=# SELECT * 
   FROM JSON_TABLE('{"a": 1}', '$' 
                   COLUMNS (
                     a bool PATH 'strict exists($.a)',
                     b bool PATH 'strict exists($.b)'
                   ));
 a | b 
---+---
 t | 
(1 row)

There is no easy way to return false without external COALESCE(),
DEFAULT false ON ERROR also does not help.  

So, I think it's worth to add EXISTS PATH clause to our implementation.




    

      

        

          

            
There is a question how to map boolean result to other data types. 

          

        

      

    

    
Now, boolean result can be used in JSON_TABLE columns of bool, int4, text, 
json[b], and other types which have CAST from bool:


    
SELECT * 
FROM JSON_TABLE('{"a": 1}', '$'
                COLUMNS (
                  a int PATH 'exists($.a)',
                  b text PATH 'exists($.b)'
                ));
 a |   b 
---+-------
 1 | false
(1 row)



    

      

        

          

            
b) When searched value is not scalar, then it returns null. This behave can be 
suppressed by clause FORMAT Json. I found a different behave, and maybe I found
a bug.  On MySQL this clause is by default for JSON values (what has sense).

          

          

            
SELECT *
 FROM
      JSON_TABLE(
        '[{"a":[1,2]}]',
        '$[*]'
        COLUMNS(
         aj JSON PATH '$.a' DEFAULT '{"x": 333}' ON EMPTY 
        )
      ) AS tt;

          

          

            
It returns null, although it should to return [1,2]. 

          

        

      

    

    
Yes, regular (non-formatted) JSON_TABLE columns can accept only scalar values. 
Otherwise an error is thrown, which can be caught by ON ERROR clause. This 
behavior is specified by the standard.

FORMAT JSON is not implicitly added for json[b] columns now. The current SQL
standard does not have any json data types, so I think we can add implicit 
FORMAT JSON for json[b] typed-columns.  But I'm a bit afraid that different 
behavior can be standardized after introduction of json data types in SQL.



    

      

        

          

            
There is another bug maybe. Although there is DEFAULT clause. It returns NULL.

          

        

      

    

    
ON ERROR should be used if "not a scalar" error needs to be caught:

SELECT *
FROM
    JSON_TABLE(
        '[{"a":[1,2]}]',
        '$[*]'
        COLUMNS(
            aj JSON PATH '$.a' DEFAULT '{"x": 333}' ON ERROR
        )
    ) AS tt;

     aj     
------------
 {"x": 333}
(1 row)


ON EMPTY catches only empty-result case (for example, non-existent path in 
lax mode):

SELECT *
FROM
    JSON_TABLE(
        '[{"a":[1,2]}]',
        '$[*]'
        COLUMNS(
            aj JSON PATH '$.foo' DEFAULT '{"x": 333}' ON EMPTY 
        )
    ) AS tt;
     aj     
------------
 {"x": 333}
(1 row)




    

      

        

          

            
I got correct result when I used FORMAT JSON clause. 
I think it should be default behave for json and jsonb columns.

          

        

      

    

    
I agree that FORMAT JSON could be implicit for json[b] columns.  But I think
there could be one minor problem if we want to verify that returned value is 
scalar.

Without FORMAT JSON this is verified by the underlying JSON_VALUE expression:

SELECT *
FROM
    JSON_TABLE(
        '[{"a":[1,2]}]',
        '$[*]'
        COLUMNS (
            aj JSON PATH 'lax $.a' ERROR ON ERROR 
        )
    ) AS tt;
ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item

(This error message with the reference to implicit JSON_VALUE needs to be fixed.)


But with FORMAT JSON we need to construct complex jsonpath with a filter and 
override ON EMPTY behavior:

SELECT *
FROM
    JSON_TABLE(
        '[{"a":[1,2]}]',
        '$[*]'
        COLUMNS (
            aj JSON FORMAT JSON 
            -- strict mode is mandatory to prevent array unwrapping
            PATH 'strict $.a ? (@.type() != "array" && @.type() != "object")'
            ERROR ON EMPTY ERROR ON ERROR
        )
    ) AS tt;
ERROR:  no SQL/JSON item



    

      

        

          

            
Another question - when I used FORMAT JSON clause, then I got syntax error
on DEFAULT keyword .. . Is it correct? 

Why I cannot to use together FORMAT JSON and DEFAULT clauses?

          

        

      

    

    
JSON_TABLE columns with FORMAT JSON, like JSON_QUERY, can have only 
ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT behaviors.

This syntax is specified in the SQL standard:

<JSON table formatted column definition> ::=
  <column name> <data type> FORMAT <JSON representation>
  [ PATH <JSON table column path specification> ]
  [ <JSON table formatted column wrapper behavior> WRAPPER ]
  [ <JSON table formatted column quotes behavior> QUOTES [ ON SCALAR STRING ] ]
  [ <JSON table formatted column empty behavior> ON EMPTY ]
  [ <JSON table formatted column error behavior> ON ERROR ]

<JSON table formatted column empty behavior> ::=
  ERROR
  | NULL
  | EMPTY ARRAY
  | EMPTY OBJECT

<JSON table formatted column error behavior> ::=
  ERROR
  | NULL
  | EMPTY ARRAY
  | EMPTY OBJECT


But I also think that DEFAULT clause could be very useful in JSON_QUERY and 
formatted JSON_TABLE columns.



    

      

        

          

            
Note - this behave is not described in documentation.

          

        

      

    

    
There are references to JSON_QUERY and JSON_VALUE behavior in the definitions 
of JSON_TABLE columns, but their behavior still seems to be unclear.  This 
needs to be fixed.


    

    
-- 

      Nikita Glukhov

      Postgres Professional: http://www.postgrespro.com

      The Russian Postgres Company

    

  


--------------A8D0389F728CB3026BE27FC0--