Re: remaining sql/json patches

[Alvaro Herrera]

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/