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

From Amit Langote
Subject Re: remaining sql/json patches
Date
Msg-id CA+HiwqGtGuPwLMQ7AW7PQ7FWQNk1QDufM6b7NoSzV597LgFpKQ@mail.gmail.com
Whole thread Raw
In response to Re: remaining sql/json patches  (Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>)
Responses Re: remaining sql/json patches
List pgsql-hackers
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
shoulderror 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:

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



pgsql-hackers by date:

Previous
From: Aleksander Alekseev
Date:
Subject: Re: Catalog domain not-null constraints
Next
From: Bertrand Drouvot
Date:
Subject: Re: Introduce XID age and inactive timeout based replication slot invalidation