Re: remaining sql/json patches - Mailing list pgsql-hackers

From Alvaro Herrera
Subject Re: remaining sql/json patches
Date
Msg-id 202403111534.xii7yxswadef@alvherre.pgsql
Whole thread Raw
In response to Re: remaining sql/json patches  (Shruthi Gowda <gowdashru@gmail.com>)
Responses Re: remaining sql/json patches
List pgsql-hackers
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/



pgsql-hackers by date:

Previous
From: Shruthi Gowda
Date:
Subject: Re: remaining sql/json patches
Next
From: Jelte Fennema-Nio
Date:
Subject: Re: UUID v7