Snowflake Stored Procedure Error for JSON input

Question

I am trying to parse JSON value and getting Syntax Error in stored procedure.

This SELECT statement works fine:

SELECT 
    parse_json ('{"fName":"Pink","lName":"Panther"}') AS json_data, 
    json_data:fName::string AS first_name, 
    json_data:lName::string AS last_name;

While trying same thing in stored procedure, I am getting a syntax error:

CREATE OR REPLACE PROCEDURE extract_json(input_json varchar)
RETURNS TABLE (res varchar)
LANGUAGE SQL
AS
$$
DECLARE
    qry string;
    res resultset;
BEGIN
    qry := 'SELECT parse_json('||:input_json||') AS json_data::sting, json_data:fName::string';
    res := (execute immediate qry);
    return table(res);
END;
$$
;

CALL extract_json('{"fName":"Pink","lName":"Panther"}');

Expected out, 2 columns only:

FIRST_NAME  LAST_NAME
Pink        Panther

Any help is appreciated. Thanks

Answer 1

The are few issues with the code:

a) resulset contains two column so (res varchar) will not work

b) alias json_data::sting cannot be casted

c) missing ' around input_json, ideally it should be bind parameter

CREATE OR REPLACE PROCEDURE extract_json(input_json varchar)
RETURNS TABLE (col VARIANT, res varchar)
LANGUAGE SQL
AS
$$
DECLARE
    qry string;
    res resultset;
BEGIN
    qry := 'SELECT parse_json('''||:input_json||''') AS json_data, json_data:fName::string';
    res := (execute immediate qry);
    return table(res);
END;
$$
;

CALL extract_json('{"fName":"Pink","lName":"Panther"}');

Output:

---

Expected out, 2 columns only:

CREATE OR REPLACE PROCEDURE extract_json(input_json varchar)
RETURNS TABLE (firstName VARCHAR, lastName varchar)
LANGUAGE SQL
AS
$$
DECLARE
    qry string;
    res resultset;
BEGIN
    qry := 'SELECT json_data:fName::string, json_data:lName::string FROM (SELECT parse_json('''||:input_json||''') AS json_data)';
    res := (execute immediate qry);
    return table(res);
END;
$$;

CALL extract_json('{"fName":"Pink","lName":"Panther"}');

Output: