How to merge variant column data from two different tables in snowflake

1
votes

I have two tables with around thousands of column each.Table contains variant data columns.I want to merge the variant data column based on key value in variant data.

eg:

Table 1 column name: SRC value : {col1:val1,col2:val2,col3:val3}

Table 2: column name: SRC value : {col1:val1,col2:val2,col4:val4}

I want output after merging : Table 1 : SRC = {col1:val1,col2:val2,col3:val3,col4:val4}

I want existing keys in variant column to update and new keys to insert in table.

I have tried it with object_insert() method but it can update a single field at a time and it is being hard to implement syntactically for thousand of columns.How to achieve this with multiple fields. Can anyone help me on this?

2
jsonmergesnowflake-cloud-data-platformvariant
The only way to leverage a MERGE function would be to flatten your 2 source tables and your final target table, and then merge them...and then transpose them back to a VARIANT. Not a great methodology. If you know what data in the VARIANT that you are using, it might make more sense to permanently flatten your final target table. Then, you can flatten and merge into that pretty easily. Is there a reason you are leaving your final table as a VARIANT? – Mike Walton
I was facing column size limitation as i couldn't store more than 16mb in table columns.That is why my final table is VARIANT. – Anvita

2 Answers

2
votes

If you know the keys in advance, and the variants are objects, then this would work:

with 
a as ( select 1 id, parse_json('{"col1":"val1","col2":"val2","col3":"val3"}')::variant src),
b as ( select 1 id, parse_json('{"col1":"val1","col2":"val2","col4":"val4"}')::variant src)
select 
  object_construct(
    'col1', coalesce(b.src:col1,a.src:col1), 
    'col2', coalesce(b.src:col2,a.src:col2), 
    'col3', coalesce(b.src:col3,a.src:col3), 
    'col4', coalesce(b.src:col4,a.src:col4)
  ) as src
from a left join b on a.id=b.id;

The output is:

SRC              
-----------------
{   "col1": "val1",   "col2": "val2",   "col3": "val3",   "col4": "val4" }

If you don't know the keys in advance, you could flatten the objects, join on the keys, coalesce() the values, and use object_agg() to assemble the combined objects.

Hope that helps

1
votes

You can use a Javascript UDF to do this. Here is a naive example that you can build on that does a very simple shallow merge:

-- Create an example table
create or replace transient table test_table (column1 VARIANT, column2 VARIANT);

-- Insert some sample data
INSERT OVERWRITE INTO test_table (column1, column2)
select PARSE_JSON('{"a": "row1_val_a", "c": "row1_val_c"}'), PARSE_JSON('{"b": "row1_val_b"}')
union all
select PARSE_JSON('{"a": "row2_val_a", "b": "row2_val_b"}'), PARSE_JSON('{"c": "row2_val_c"}')
;

-- Have a look at the table
select * from test_table;

-- Create the javascript UDF that merges the two columns
CREATE OR REPLACE FUNCTION merge_json(OBJ_1 VARIANT, OBJ_2 VARIANT)
    RETURNS variant
    LANGUAGE JAVASCRIPT
    AS 
    $$
    function extend(obj, src) {
      for (var key in src) {
          if (src.hasOwnProperty(key)) obj[key] = src[key];
      }
      return obj;
    }
    return extend(OBJ_1, OBJ_2)
    $$;

-- Use the UDF in a SQL query
select merge_json(column1, column2) from test_table;

This example assumes that the VARIANT objects are in the same table just in separate columns. You could change it to grab the Variants from different tables pretty easily.