snowflake - parse_json

Question

I have a sql query in which i want to filter in where clause different rows following the next logic, for example:

where table1.field1 in (function1(:json_name))

So the function, function1 is defined as:

CREATE FUNCTION IF NOT EXISTS db.schema.function1(json_name VARCHAR) 
RETURNS TABLE(json_col VARCHAR) 
AS $$ 
DECLARE 
    exception1 EXCEPTION (-20001, 'Error'); 
BEGIN
    select parse_json(:json_name as src) src, lateral flatten(input => src) ) 
    where value is not null; 
END; $$;

But i'm getting errors like,

Syntax error:L compilation error: (line 3)
syntax error line 3 at position 4 unexpected 'exception1'.
syntax error line 3 at position 29 unexpected '-'.
syntax error line 3 at position 35 unexpected ','. (line 3)

thoughts?

Answer 1

For snowflake I suggest using javascript such as this:

CREATE OR REPLACE FUNCTION db.schema.function1(json_name STRING)
RETURNS TABLE(json_col STRING)
LANGUAGE JAVASCRIPT
AS
$$
    var parsed = JSON.parse(json_name);
    var flattened = [];
    
    for (var key in parsed) {
        if (parsed.hasOwnProperty(key) && parsed[key] !== null) {
            flattened.push([parsed[key]]);
        }
    }
    
    return flattened;
$$;

And call it like this:

SELECT *
FROM table1 t
WHERE t.field1 IN (SELECT * FROM TABLE(db.schema.function1(:json_name)));

You will need to correct the "db.schema" references, and you may want to include a "try/catch" for error handling e.g:

CREATE OR REPLACE PROCEDURE db.schema.function1(json_name STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
    var flattened = [];
    try {
        var parsed = JSON.parse(json_name);
        for (var key in parsed) {
            if (parsed.hasOwnProperty(key) && parsed[key] !== null) {
                flattened.push(parsed[key]);
            }
        }
    } catch (err) {
        // handle error here
        return "Error: " + err.message;
    }
    return flattened.join(',');
$$;