Re: remaining sql/json patches

On Mon, Mar 18, 2024 at 3:33 PM Amit Langote <amitlangote09(at)gmail(dot)com>
wrote:

> Himanshu,
>
> On Mon, Mar 18, 2024 at 4:57 PM Himanshu Upadhyaya
> <upadhyaya(dot)himanshu(at)gmail(dot)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