Split a string into columns and fetch the columns from a json object

Question

I have a string separated by commas, which are the column names in my_table. “First_Name, Last_Name, Age”. I need to split the string with commas and fetch the columns(First_Name, Last_Name, Age) from my_table from a JSON column called custom_fields.

Here is the JSON column below:

custom_fields
{
"First_Name": "Vaibhav",
"Last_Name": "Terli",
"Age": "24"
}

Expected Output:

First_Name | Last_Name | Age
---------------------------
Vaibhav   | Terli     | 24

Answer 1

It is easier if your JSON in a VARIANT type, then you can use standard Snowflake syntax to call values from individual JSON keys. If your JSON is a string, you can use PARSE_JSON function to convert it to VARIANT, see below example:

-- Initialize some test data set.
CREATE TABLE json_data_table (
    user_data VARIANT
);

-- Insert your data converting to VARIANT using PARSE_JSON function
INSERT INTO json_data_table (user_data)
SELECT PARSE_JSON('{
    "First_Name": "Vaibhav",
    "Last_Name": "Terli",
    "Age": "24"
}') AS user_data;

Now you can select individual keys as follows:

SELECT 
    user_data:First_Name AS First_Name,
    user_data:Last_Name AS Last_Name,
    user_data:Age AS Age
FROM json_data_table;

The expected output: