Obsolete
Status Update
No update yet.
Comments
to...@tillerhq.com
Edit:
- Spurious line "65 / ~350" after the first paragraph
- Spurious line "65 / ~350" after the first paragraph
[Deleted User] <[Deleted User]>
+1
to...@tillerhq.com
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;
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;
[Deleted User] <[Deleted User]>
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.
bh...@gmail.com
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.
jo...@google.com
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.
jo...@google.com
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"]}');
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"]}');
Description
First, please search through existing issues to ensure that the bug has not already been reported. You can start the search here:
If the issue has already been reported, you can click the star next to the issue number to subscribe and receive updates. We prioritize responding to the issues with the most stars. You can also comment on the issue to provide any details of your experience with it.
If your issue has not been reported, please provide all of the following:
-----------------------------------------------------------------------------------------------------------------------------
A short description of the issue:
When an incorrect range is declared the error reported by the IDE and email reports include an incorrect line number.
A small code sample that reliably reproduces the issue. The sample should run as-is or with minimal setup, without external dependencies.
function Broken() {
// get details of this spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sortObj =[];
var currentInterSheet = ss.getSheetByName("Sheet1");
var lastInterRow = currentInterSheet.getLastRow();
var lastInterColumn = currentInterSheet.getLastColumn();
// Invalid range defined
var sortRange = currentInterSheet.getRange(2,1,lastInterRow, lastInterColumn);
var jj = 1;
sortObj.push({column: jj, ascending: true});
if (sortObj.length) {
//Invalid range used
sortRange.sort(sortObj);
}
// different range defined
var detailSheet = ss.getSheetByName('Sheet3');
detailSheet.sort([1]);
// Error generated on this line
var range = detailSheet.getDataRange();
}
What steps will reproduce the problem?
1. Run that code
2. "The coordinates of the range are outside the dimensions of the sheet." error reported at line " var range = detailSheet.getDataRange();"
3.
What is the expected output? What do you see instead? If you see error messages, please provide them.
Expect that the error will be generated and reported on a line where the invalid range is defined or used.
Instead, the error is reported as occurring on a subsequent unrelated line relating to a different range.
Please provide any additional information below.