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
