CODEWITHSUNDEEP
jsonobjectsnowflake-cloud-data-platformcase-sensitivelowercase
Angie
Angie

Reputation: 327

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)

Upvotes: 4

Views: 958

Answers (3)

Marco Roy
Marco Roy

Reputation: 5243

If you work with a tool like DBT, you could create a macro which calls OBJECT_CONSTRUCT() with all of the columns listed out. All you'd have to do is to document the columns in your model's YAML file, and then you could grab them via model.columns in the macro.

That is probably what would offer the best performance.

Upvotes: 0

Marco Roy
Marco Roy

Reputation: 5243

You can also do this with a very simple JavaScript UDF:

CREATE OR REPLACE FUNCTION lower_object_keys(
  OBJ OBJECT
)
  RETURNS OBJECT
  LANGUAGE JAVASCRIPT
  IMMUTABLE
AS
$$
  return Object.fromEntries(
    Object.entries(OBJ).map(
      ([k, v]) => [k.toLowerCase(), v]
    )
  );
$$;

And then you can easily do:

SELECT lower_object_keys(OBJECT_CONSTRUCT(*))
FROM ...

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

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:

enter image description here

Upvotes: 5

Related Questions