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?
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 |
---------------+