Re: remaining sql/json patches - Mailing list pgsql-hackers

From Himanshu Upadhyaya
Subject Re: remaining sql/json patches
Date
Msg-id CAPF61jBKtQb31cP+X3Aaiwbj0ZkyTajrBMXz2zBhjVPW3a6Ktw@mail.gmail.com
Whole thread Raw
In response to Re: remaining sql/json patches  (Amit Langote <amitlangote09@gmail.com>)
List pgsql-hackers


On Mon, Mar 18, 2024 at 3:33 PM Amit Langote <amitlangote09@gmail.com> wrote:
Himanshu,

On Mon, Mar 18, 2024 at 4:57 PM Himanshu Upadhyaya
<upadhyaya.himanshu@gmail.com> wrote:
> I have tested a nested case  but  why is the negative number allowed in subscript(NESTED '$.phones[-1]'COLUMNS), it should error out if the number is negative.
>
> ‘postgres[170683]=#’SELECT * FROM JSON_TABLE(jsonb '{
> ‘...>’         "id" : "0.234567897890",
> ‘...>’         "name" : { "first":"Johnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn", "last":"Doe" },
> ‘...>’         "phones" : [{"type":"home", "number":"555-3762"},
> ‘...>’                     {"type":"work", "number":"555-7252", "test":123}]}',
> ‘...>’                '$'
> ‘...>’                COLUMNS(
> ‘...>’                     id numeric(2,2) PATH 'lax $.id',
> ‘...>’                     last_name varCHAR(10) PATH 'lax $.name.last', first_name VARCHAR(10) PATH 'lax $.name.first',
> ‘...>’                      NESTED '$.phones[-1]'COLUMNS (
> ‘...>’                    "type" VARCHAR(10),
> ‘...>’                    "number" VARCHAR(10)
> ‘...>’ )
> ‘...>’      )
> ‘...>’   ) as t;
>   id  | last_name | first_name | type | number
> ------+-----------+------------+------+--------
>  0.23 | Doe       | Johnnnnnnn |      |
> (1 row)

You're not getting an error because the default mode of handling
structural errors in SQL/JSON path expressions is "lax".  If you say
"strict" in the path string, you will get an error:


ok, got it, thanks.
 
SELECT * FROM JSON_TABLE(jsonb '{
         "id" : "0.234567897890",
         "name" : {
"first":"Johnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn",
"last":"Doe" },
         "phones" : [{"type":"home", "number":"555-3762"},
                     {"type":"work", "number":"555-7252", "test":123}]}',
                '$'
                COLUMNS(
                     id numeric(2,2) PATH 'lax $.id',
                     last_name varCHAR(10) PATH 'lax $.name.last',
first_name VARCHAR(10) PATH 'lax $.name.first',
                      NESTED 'strict $.phones[-1]'COLUMNS (
                    "type" VARCHAR(10),
                    "number" VARCHAR(10)
 )
      ) error on error
   ) as t;
ERROR:  jsonpath array subscript is out of bounds

--
Thanks, Amit Langote


--
Regards,
Himanshu Upadhyaya
EnterpriseDB: http://www.enterprisedb.com

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Built-in CTYPE provider
Next
From: "Hayato Kuroda (Fujitsu)"
Date:
Subject: RE: speed up a logical replica setup