Re: remaining sql/json patches

On Wed, Mar 6, 2024 at 9:04 PM Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>
wrote:

>
>
> I'm pretty sure this is the correct & expected behavior. The second
> query treats the value as string (because that's what should happen for
> values in double quotes).
>
> ok, Then why does the below query provide the correct conversion, even if
we enclose that in double quotes?
‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb '{
"id" : "1234567890",
"FULL_NAME" : "JOHN DOE"}',
'$'
COLUMNS(
name varchar(20) PATH 'lax $.FULL_NAME',
id int PATH 'lax $.id'
)
)
;
name | id
----------+------------
JOHN DOE | 1234567890
(1 row)

and for bigger input(string) it will leave as empty as below.
‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb '{
"id" : "12345678901",
"FULL_NAME" : "JOHN DOE"}',
'$'
COLUMNS(
name varchar(20) PATH 'lax $.FULL_NAME',
id int PATH 'lax $.id'
)
)
;
name | id
----------+----
JOHN DOE |
(1 row)

seems it is not something to do with data enclosed in double quotes but
somehow related with internal casting it to integer and I think in case of
bigger input it is not able to cast it to integer(as defined under COLUMNS
as id int PATH 'lax $.id')

‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb '{
"id" : "12345678901",
"FULL_NAME" : "JOHN DOE"}',
'$'
COLUMNS(
name varchar(20) PATH 'lax $.FULL_NAME',
id int PATH 'lax $.id'
)
)
;
name | id
----------+----
JOHN DOE |
(1 row)
)

if it is not able to represent it to integer because of bigger input, it
should error out with a similar error message instead of leaving it empty.

Thoughts?

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