Feedback-ID: ia2694551:Fastmail
Date: Mon, 11 Mar 2024 16:34:31 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Shruthi Gowda <gowdashru@gmail.com>
Cc: jian he <jian.universality@gmail.com>, 
	Amit Langote <amitlangote09@gmail.com>, Andres Freund <andres@anarazel.de>, 
	Andrew Dunstan <andrew@dunslane.net>, Erik Rijkers <er@xs4all.nl>, 
	PostgreSQL-development <pgsql-hackers@postgresql.org>, Tomas Vondra <tomas.vondra@enterprisedb.com>
Subject: Re: remaining sql/json patches
Message-ID: <202403111534.xii7yxswadef@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAASxf_MW4RBUYuS3ybbJYH9U7LQ5cjT699naiG+xKX8g=7Ut2g@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/202403111534.xii7yxswadef%40alvherre.pgsql>
Precedence: bulk

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/