Snowflake - challenge in accessing objects in an array

Question

Snowflake column tag has following value.

Using dot notation I want to create columns status, date, category & creditDebit.

I can't use this method to access the value because sequence of objects in the array can change among rows.

tag[0].status::VARCHAR,tag[0].date::VARCHAR,tag[0].category::VARCHAR,tag[0].creditDebit::VARCHAR

How to extract values? Lateral flatten is an option but challenge is it create 4 different rows from a single array.

[
    {
        "status": "pending"
    },
    {
        "date": "01-02-2000"
    },
    {
        "category": "Third Party Payment Providers"
    },
    {
        "creditDebit": "debit"
    }
]

Answer 1

If the structure of the JSON is a simple, flat array of name-value pairs, this Javascript UDF can extract the value for the name of the key. It finds it by looping instead of by flattening rows and filtering. This UDF's approach can be higher performance vs. flattening and filtering.

create or replace table T1(V variant);

insert into T1 select parse_json($$
[
    {
        "status": "pending"
    },
    {
        "date": "01-02-2000"
    },
    {
        "category": "Third Party Payment Providers"
    },
    {
        "creditDebit": "debit"
    }
]
    $$);
    
create or replace function GET_VALUE_IN_ARRAY(ARR array, KEY_NAME string)
returns string 
language javascript as
$$

for (let i = 0; i < ARR.length; i++) {
    if (Object.keys(ARR[i])[0] === KEY_NAME) {
        return ARR[i][KEY_NAME];
    }
}

$$;

select get_value_in_array(V, 'creditDebit') as CARD_TYPE from T1;

If the structure of the variant/array is different, changes to the Javascript can handle that.