Re: remaining sql/json patches

On 2024-Mar-11, Shruthi Gowda wrote:

> *CASE 2:*
> ------------------
> SELECT * FROM JSON_TABLE(jsonb '{
> "id" : 901,
> "age" : 30,
> "*FULL_NAME*" : "KATE DANIEL"}',
> '$'
> COLUMNS(
> FULL_NAME varchar(20),
> ID int,
> AGE int
> )
> ) as t;

I think this is expected: when you use FULL_NAME as a SQL identifier, it
is down-cased, so it no longer matches the uppercase identifier in the
JSON data. You'd have to do it like this:

SELECT * FROM JSON_TABLE(jsonb '{
"id" : 901,
"age" : 30,
"*FULL_NAME*" : "KATE DANIEL"}',
'$'
COLUMNS(
"FULL_NAME" varchar(20),
ID int,
AGE int
)
) as t;

so that the SQL identifier is not downcased.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/