I have copied some json files into Snowflake from a stage and I have a property name which contains a hyphen.
When I try to query for this property name (as shown below), I get this error.
select my_variant:test-id from mytable;
SQL compilation error: error line 1 at position 44 invalid identifier 'ID'.
I assume it doesn't like the hyphen. Is there any way I can rename this hyphenated name in my variant column so I don't get the error?
You just need to quote the column name in the variant:
select my_variant:"test-id" from mytable;
If you want to update it, see below. It assumes that you have a key per row, so that we can aggregate it back to rebuild the variant at the row level.
Setup test table:
create or replace table test (k int, a variant);
insert into test
select 1, parse_json('{"test-id": 1, "test-id2": "2"}')
union all
select 2, parse_json('{"test-1": 1, "test-2": "2"}');
select * from test;
+---+-------------------+
| K | A |
|---+-------------------|
| 1 | { |
| | "test_id": 1, |
| | "test_id2": "2" |
| | } |
| 2 | { |
| | "test_1": 1, |
| | "test_2": "2" |
| | } |
+---+-------------------+
Update the table:
update test t
set t.a = b.value
from (
with t as (
select
k,
replace(f.key, '-', '_') as key,
f.value as value
from test,
lateral flatten(a) f
)
select
k, object_agg(key, value) as value
from t
group by k
) b
where t.k = b.k
;
select * from test;
+---+-------------------+
| K | A |
|---+-------------------|
| 1 | { |
| | "test_id": 1, |
| | "test_id2": "2" |
| | } |
| 2 | { |
| | "test_1": 1, |
| | "test_2": "2" |
| | } |
+---+-------------------+