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?