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

Question

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')));

Answer 1

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:

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));