Summing values from a JSON array in Snowflake

Question

I have a source data which contains the following type of a JSON array:

[
  [
    "source 1",
    250    
  ],
  [
    "other source",
    58
  ],
  [
    "more stuff",
    42
  ],
  ...
]

There can be 1..N pairs of strings and values like this. How can I sum all the values together from this JSON?

Darbydarce

Answer 1

Note: The answer below is outdated, a preferred solution is to use REDUCE as suggested by Lukasz.

You can use FLATTEN, it will produce a single row for each element of the input array. Then you can access the number in that element directly.

Imagine you have this input table:

create or replace table input as
select parse_json($$
[
  [
    "source 1",
    250    
  ],
  [
    "other source",
    58
  ],
  [
    "more stuff",
    42
  ]
]
$$) as json;

FLATTEN will do this:

select index, value from input, table(flatten(json));
-------+-------------------+
 INDEX |       VALUE       |
-------+-------------------+
 0     | [                 |
       |   "source 1",     |
       |   250             |
       | ]                 |
 1     | [                 |
       |   "other source", |
       |   58              |
       | ]                 |
 2     | [                 |
       |   "more stuff",   |
       |   42              |
       | ]                 |
-------+-------------------+

And so you can just use VALUE[1] to access what you want

select sum(value[1]) from input, table(flatten(json));
---------------+
 SUM(VALUE[1]) |
---------------+
 350           |
---------------+

Jampack

Finally REDUCE function is supported and the code is much cleaner. – Ramble

Answer 2

Using higher-order function REDUCE:

SELECT col, REDUCE(col, 0, (acc, x) -> acc + x[1]::NUMBER) AS col_sum
FROM tab;

For input:

CREATE OR REPLACE TABLE tab(col VARIANT)
AS
SELECT [
  ['source 1', 250 ],
  ['other source',58],
  ['more stuff',42]
];

Output:

Ramble

This is solutions is by far better than the old one – Jampack