google-bigquerybigquery-udf

Syntax error: Unexpected keyword UNNEST at [12:8] while using unnest


I want to use unnest in the following function to use INkeyword but it is throwing error unexpected keyword UNNEST while using unnest.

  CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
        RETURNS STRING
        LANGUAGE js AS """
            try { var parsed = JSON.parse(json);
                return JSON.stringify(jsonPath(parsed, json_path));
            } catch (e) { return null }
        """
        OPTIONS (
            library="https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/jsonpath/jsonpath-0.8.0.js.txt"
        );
SELECT UNNEST((CUSTOM_JSON_EXTRACT( '''[
    {
      "mobile_ad_id1": "409ca39f-447e-4700-9ab1-fb3f743c2a04",
      "key":1
    },
    {
      "mobile_ad_id1": "0f5aef1c-d957-41b7-91f8-af51f0c775bf",
      "key":1
    }
  ]''', '$[?(@.key=="1")].mobile_ad_id1')));

Solution

  • UNNEST should be used together with an UDF which returns an array. Try this one instead.

    CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
    RETURNS ARRAY<STRING>
    LANGUAGE js AS """
      try { 
        var parsed = JSON.parse(json);
        return jsonPath(parsed, json_path);
      } catch (e) { return null; }
    """
    OPTIONS (
        library="https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/jsonpath/jsonpath-0.8.0.js.txt"
    );
    SELECT * 
      FROM UNNEST((CUSTOM_JSON_EXTRACT('''[
                      { "mobile_ad_id1": "409ca39f-447e-4700-9ab1-fb3f743c2a04", "key":1 },
                      { "mobile_ad_id1": "0f5aef1c-d957-41b7-91f8-af51f0c775bf", "key":1 }]
                  ''', '$[?(@.key=="1")].mobile_ad_id1'))
                 );
    

    output:

    enter image description here

    update:

    DECLARE json_data DEFAULT '''
      [ { "mobile_ad_id1": "409ca39f-447e-4700-9ab1-fb3f743c2a04", "key":1 },
        { "mobile_ad_id1": "0f5aef1c-d957-41b7-91f8-af51f0c775bf", "key":1 } ]
    ''';
    
    DECLARE json_path DEFAULT '$[?(@.key=="1")].mobile_ad_id1';
    
    CREATE TEMP TABLE mytable AS
    SELECT "409ca39f-447e-4700-9ab1-fb3f743c2a04" AS mobile_ad_id;
    
    CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
    RETURNS ARRAY<STRING>
    LANGUAGE js AS """
      try { 
        var parsed = JSON.parse(json);
        return jsonPath(parsed, json_path);
      } catch (e) { return null; }
    """
    OPTIONS (
        library="https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/jsonpath/jsonpath-0.8.0.js.txt"
    );
    
    SELECT *
      FROM `mytable`
     WHERE mobile_ad_id IN UNNEST(CUSTOM_JSON_EXTRACT(json_data, json_path));
    

    enter image description here