Question 235053 in Javascript Question posted in
A very good W3school tutorial can be found here.

Convert Python function to Javascript

I have created a user-defined function in Python in my Snowflake db to parse specific JSON key.

create or replace function get_tag(tag_json string, tag_key STRING)
RETURNS string
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('json5')
HANDLER = 'get_tag_py'
AS
$$
import json
def get_tag_py(json_data, key):
    data = json.loads(json_data)
    value = data.get(key)
    return value
$$;

It works as expected but the performance is quite slow. I converted the function to JavaScript as shown below:

CREATE OR REPLACE FUNCTION  get_tag_js(json_data string, key string)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
var json_data, key;
var parsedData = JSON.parse(json_data);
value = parsedData[key];
return value;
$$
;

It fails at runtime with the following error:

select get_tag_js(json_column, 'MY_KEY') from my_orders_tbl;
JavaScript execution error: Uncaught SyntaxError: "undefined" is not valid JSON in GET_TAG_JS at 'undefined' position 0

Can someone please tell me what I am missing here?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I removed the “var json_ data, key;” line and made “json_data” and “key” parameters in the function definition and everywhere else in upper case letters only.

    CREATE OR REPLACE FUNCTION get_tag_js(json_data string, key string)
  RETURNS string
  LANGUAGE JAVASCRIPT
  AS '
  // Define a function 
  function get_tag(JSON_DATA, KEY) {
    var parsedData = JSON.parse(JSON_DATA);
    value = parsedData[KEY];
    return value;
  }

  // Now call the function that we defined earlier.
  return get_tag(JSON_DATA, KEY);
  ';

    It works as expected. Now I need to mod it to take a key regardless of case.


  2. 0

    Answer to your Question:

    Your function should not declare the same named parameters, this is why the are undefined. Also the doc’s state the parameters will be UPPER CASE, because that is how all tokens are treated in SQL, thus in your JS they need to be upper case:

    CREATE OR REPLACE FUNCTION  get_tag_js(json_data string, key string)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
    var parsedData = JSON.parse(JSON_DATA);
    value = parsedData[KEY];
    return value;
$$
;

    and now we can use those

    select get_tag_js(json_column, 'MY_KEY') 
from values 
  ('{"key1":"value1", "MY_KEY":1235}'),
  ('{"key1":"value1", "my_key":22356}')
    as t(json_column)
;

    enter image description here

    If you really want lower case parameters in the function (that should not exist as shown (but this could be a toy example)) then use quotes to force them to lower case like:

    CREATE OR REPLACE FUNCTION  get_tag_js("json_data" string, "key" string)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
    var parsedData = JSON.parse(json_data);
    value = parsedData[key];
    return value;
$$
;

    Case insensitive, can be achieved by pulling the object apart and doing a case insensitive match. Which is more likely still faster than the exact same steps in JavaScript/Python

    select 
    get_tag_js(json_column, 'MY_KEY') as r1
    ,GET(PARSE_JSON(json_column), 'MY_KEY') as r2
    ,GET_PATH(PARSE_JSON(json_column), 'MY_KEY') as r3
    ,f.value as r4
from values 
('{"key1":"value1", "MY_KEY":1235}'),
('{"key1":"value1", "my_key":22356}')
as t(json_column)
cross join table(flatten(input=>PARSE_JSON(json_column))) as f
where f.key ilike 'MY_KEY'

    enter image description here

    Login or Signup to reply.
  3. 0

    There is no need to create a UDF to parse JSON in Snowflake. For simple JSON, you may query the key directly

    select json_column:MY_KEY from my_orders_tbl

    For more complex JSON, you may need to use FLATTEN. See Querying Semi-structured Data for more information.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search