json object snowflake-cloud-data-platform case-sensitive lowercase

Making json keys lowercase Snowflake

I have a table that looks like

ID	DATE	FIRST_NAME	H&P
12	2023-02-01	JESSICA	40
99	2023-02-03	MINA	98
12	2023-01-14	JACK	12

I've created another table containing these columns as columns by doing

SELECT ID, OBJECT_CONSTRUCT(*) as details
FROM MY_TABLE;

ID	DETAILS
12	{"DATE":"2023-02-01", "FIRST_NAME":"JESSICA", "H&P":"40"}
99	{"DATE":"2023-02-03", "FIRST_NAME":"MINA", "H&P":"98"}
12	{"DATE":"2023-01-14", "FIRST_NAME":"JACK", "H&P":"12"}

Is there a way to modify the DETAILS column when using OBJECT_CONSTRUCT() so that the KEYS of the json are lowercase but the VALUES stay the same as they appear, so I could get something like

ID	DETAILS
12	{"date":"2023-02-01", "first_name":"JESSICA", "h&p":"40"}
99	{"date":"2023-02-03", "first_name":"MINA", "h&p":"98"}
12	{"date":"2023-01-14", "first_name":"JACK", "h&p":"12"}

thanks! (I would like to do this without listing out the columns as my actual table has about 400 columns)

Solution

Keys could be listed explicitly as lower cased:

CREATE OR REPLACE TABLE MY_TABLE(ID INT, DATE DATE, FIRST_NAME TEXT, "H&P" INT)
AS
SELECT 12,  '2023-02-01', 'JESSICA', 40 UNION
SELECT 99,  '2023-02-03', 'MINA', 98 UNION
SELECT 12,  '2023-01-14', 'JACK', 12;

SELECT ID, OBJECT_CONSTRUCT('date', DATE, 
                            'fist_name', FIRST_NAME,
                            'h&p', "H&P") AS details
FROM MY_TABLE;

Output:

ID	DETAILS
12	{ "date": "2023-02-01", "fist_name": "JESSICA", "h&p": 40 }
99	{ "date": "2023-02-03", "fist_name": "MINA", "h&p": 98 }
12	{ "date": "2023-01-14", "fist_name": "JACK", "h&p": 12 }

Using OBJECT constant syntax:

SELECT ID, {'date': DATE, 
            'fist_name': FIRST_NAME,
            'h&p': "H&P"} AS details
FROM MY_TABLE;

EDIT:

Is there a way to do it without explicitly listing the columns?

Yes, one option requires flattening and building object second time which will reduce the performance:

WITH cte AS (
  SELECT ID, OBJECT_CONSTRUCT(*) AS details
  FROM MY_TABLE
)
SELECT ID, OBJECT_AGG(LOWER(f.KEY), f.VALUE) AS details
FROM cte
,LATERAL FLATTEN(INPUT => cte.details) AS f
GROUP BY ID, SEQ;

Output: