IssueTracker

Edit:
- Spurious line "65 / ~350" after the first paragraph

+1

Could you perhaps use REGEX to parse the json string? Something like this should work (with some modifications for your use case):

WITH
  yourTable AS (
  SELECT
    '{"bar": ["vimota", ""]}' AS json
  UNION ALL
  SELECT
    '{"bar": [, "Brazil"]}' )
SELECT
  ARRAY(
  SELECT
    REGEXP_EXTRACT(num, r'"(.*)"')
  FROM
    UNNEST(SPLIT(REGEXP_EXTRACT(JSON_EXTRACT(json,
            '$.bar'), r'\[(.*)\]'))) AS num
  WHERE
    REGEXP_EXTRACT(num, r'"(.*)"') IS NOT NULL)
FROM
  yourTable;

Nope, many of our json arrays contain json string values with user-input chars like " which would break a regex-based approach to parsing the json, since we'd have to distinguish " from \" from \\" from \\\", etc.

Thanks for the feedback! We'll take this suggestion into account as we plan JSON-related functionality, and I'll update here if and when there is more to share.

Thanks! In the meantime, what's the best way to turn a json array into a bq array? Looking through the docs on json functions I don't see a way to achieve this, other than writing a custom javascript udf, which imposes the strict limitations of queries that use udfs.

The best option right now--if you need to take escaping into account--is using a JavaScript UDF. If you generally have a small number of JSON array elements and you want to handle escaped strings, you could use a hack like this one:

CREATE TEMP FUNCTION JsonExtractArray(json STRING) AS (
  (SELECT ARRAY_AGG(v IGNORE NULLS)
   FROM UNNEST([
     JSON_EXTRACT_SCALAR(json, '$.foo[0]'),
     JSON_EXTRACT_SCALAR(json, '$.foo[1]'),
     JSON_EXTRACT_SCALAR(json, '$.foo[2]'),
     JSON_EXTRACT_SCALAR(json, '$.foo[3]'),
     JSON_EXTRACT_SCALAR(json, '$.foo[4]'),
     JSON_EXTRACT_SCALAR(json, '$.foo[5]'),
     JSON_EXTRACT_SCALAR(json, '$.foo[6]'),
     JSON_EXTRACT_SCALAR(json, '$.foo[7]'),
     JSON_EXTRACT_SCALAR(json, '$.foo[8]'),
     JSON_EXTRACT_SCALAR(json, '$.foo[9]')]) AS v)
);

Even though there is an escaped quote inside the "bar" string in this example, you'll get the expected three elements:

SELECT JsonExtractArray('{"foo":[1,2,3,"ba\\"r"]}');

Yeah, hardcoding a max length on the input arrays is a non starter for us.

check - https://stackoverflow.com/a/45315826/5221944 for another option