Trying to parse json in snowflake so that the values match: "{Codes":["VP075","VP114","VP053"],"Amount":[0.83,0.51,0.51]}, there is usually multiple values and I tried to flatten both codes and amount out, but then the values do not match.
Unable to parse json fields with multiple values so that the values match using sql
Viewed 41
1 Answers
It is possible to FLATTEN twice and match on INDEX column:
INDEX The index of the element, if it is an array; otherwise NULL.
CREATE OR REPLACE TABLE t(col VARIANT) AS
SELECT PARSE_JSON('{"Codes":["VP075","VP114","VP053"],"Amount":[0.83,0.51,0.51]}');
SELECT s.value::STRING AS code, s2.value::DECIMAL(10,2) AS amount
FROM t
,TABLE(FLATTEN(input => t.col, path => 'Codes')) s
,TABLE(FLATTEN(input => t.col, path => 'Amount')) s2
WHERE s.INDEX = s2.INDEX;
/*
CODE AMOUNT
VP075 0.83
VP114 0.51
VP053 0.51
*/
Related