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

From Shruthi Gowda
Subject Re: remaining sql/json patches
Date
Msg-id CAASxf_PqyUZTo-h0C7qyGhEZQyuC6KUjqvWA4NeAYM+XT5NWnA@mail.gmail.com
Whole thread Raw
In response to Re: remaining sql/json patches  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
Responses Re: remaining sql/json patches
List pgsql-hackers
Thanka Alvaro. It works fine when quotes are used around the column name.

On Mon, Mar 11, 2024 at 9:04 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
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: Stephen Frost
Date:
Subject: Re: Statistics Import and Export
Next
From: Corey Huinker
Date:
Subject: Re: Statistics Import and Export