Can I dynamically select a variant key using a subquery, without flattening the table?

Question

Let's say I have a variant field with multiple keys, and I don't know which key will give me the value that I care about until I query a metadata table. Here's a simplified and contrived example:

create table KEY_LOOKUP (
    key_name text,
    is_useful boolean
);

insert into KEY_LOOKUP values
    ('A', True),
    ('B', False);

create table DATA (
    key_values variant
);

insert into DATA
    select PARSE_JSON('{"A": "Useful Value", "B": "Useless Value"}');

The DATA table now contains two keys, one of which will give me the value I want, and one of which is useless. If I knew in advance that A was the key I needed, I could simply select key_values:A, but if I don't know this in advance, I have to query KEY_LOOKUP first.

If I want to write a query that will always use the appropriate key, I can flatten the table and pull the relevant value like this:

select b.value from DATA a, lateral flatten(input => a.key_values) b
where b.key in
     (select key_name from KEY_LOOKUP
         where is_useful);

But this has several disadvantages. It's slow, the syntax is verbose and (to me) unintuitive, and it's awkward to deal with aggregations after the table has been flattened, especially if I'm doing this for multiple keys.

Ideally, I would love to write something like this:

select key_values:{select key_name from KEY_LOOKUP where is_useful}
from DATA

Is there a simpler approach?

Gokhan Atil Gokhan Atil · Accepted Answer · 2020-05-01T22:06:47

Actually, it's possible to write a SQL statement very similar to your dream query:

select GET(key_values, (select key_name::VARCHAR from KEY_LOOKUP
where is_useful)) from DATA;

Another reason to love Snowflake :)

Can I dynamically select a variant key using a subquery, without flattening the table?

1 Answers