Is there a way I can aggregate values within a VARIANT column in Snowflake?

Question

I would like to aggregate a VARIANT column while keeping the same structure without breaking it out then compiling again.

for example:

FEES_APPLIED

{   "FeesAppliedTotal": {     "Amount": 0.4,     "Currency": "GBP"   },   "ReceiptFee": {     "Amount": 0.1,     "Currency": "GBP"   },   "ReminderFee": {     "Amount": 0.1,     "Currency": "GBP"   },   "TransactionFee": {     "Amount": 0.2,     "Currency": "GBP"   } }
{   "FeesAppliedTotal": {     "Amount": 0.4,     "Currency": "GBP"   },   "ReceiptFee": {     "Amount": 0.1,     "Currency": "GBP"   },   "ReminderFee": {     "Amount": 0.1,     "Currency": "GBP"   },   "TransactionFee": {     "Amount": 0.2,     "Currency": "GBP"   } }
{   "FeesAppliedTotal": {     "Amount": 0.4,     "Currency": "GBP"   },   "ReceiptFee": {     "Amount": 0.1,     "Currency": "GBP"   },   "ReminderFee": {     "Amount": 0.1,     "Currency": "GBP"   },   "TransactionFee": {     "Amount": 0.2,     "Currency": "GBP"   } }
{   "FeesAppliedTotal": {     "Amount": 0.4,     "Currency": "GBP"   },   "ReceiptFee": {     "Amount": 0.1,     "Currency": "GBP"   },   "ReminderFee": {     "Amount": 0.1,     "Currency": "GBP"   },   "TransactionFee": {     "Amount": 0.2,     "Currency": "GBP"   } }

the output should be:

{   "FeesAppliedTotal": {     "Amount": 1.6,     "Currency": "GBP"   },   "ReceiptFee": {     "Amount": 0.4,     "Currency": "GBP"   },   "ReminderFee": {     "Amount": 0.4,     "Currency": "GBP"   },   "TransactionFee": {     "Amount": 0.8,     "Currency": "GBP"   } }

is this possible?

Answer 1

No, it's not possible. You should parse the JSON to get the values, do the calculations and then rebuild the JSON:

select object_agg( key, value ) as result from (
select f.key, object_construct('Amount', sum(f.VALUE:Amount::NUMBER(2,1)), 'Currency', 'GBP' ) value from my_table,
lateral flatten( v ) f
group by f.key);

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                             RESULT                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| { "FeesAppliedTotal": { "Amount": 1.6, "Currency": "GBP" }, "ReceiptFee": { "Amount": 0.4, "Currency": "GBP" }, "ReminderFee": { "Amount": 0.4, "Currency": "GBP"   }, "TransactionFee": { "Amount": 0.8, "Currency": "GBP" } } |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+