Menu
I have data like:
id col1 col2
-----------------
1 [1,2] [2,3]
2 [4,4,6] [6,7]
and I want to have data like:
id col3
---------
1 [2]
2 [6]
Any smart solutions for this?
753
To convert an ARRAY into a set of rows, also known as "flattening," use the UNNEST operator. UNNEST takes an ARRAY and returns a table with a single row for each element in the ARRAY . Because UNNEST destroys the order of the ARRAY elements, you may wish to restore order to the table.
OFFSET means that the numbering starts at zero, ORDINAL means that the numbering starts at one. A given array can be interpreted as either 0-based or 1-based. When accessing an array element, you must preface the array position with OFFSET or ORDINAL , respectively; there is no default behavior.
What are Arrays and how are they used in BigQuery: Arrays in BigQuery, like in any other language, are a collection of elements of the same data type. For example, this is what an Array address_history might look like: id:”1",
The intersection of the two arrays results in those elements that are contained in both of them. If an element is only in one of the arrays, it is not available in the intersection. An example of this is given as follows −. Array 1 = 1 2 5 8 9 Array 2 = 2 4 5 9 Intersection = 2 5 9.
With BigQuery, you can construct array literals, build arrays from subqueries using the ARRAY function, and aggregate values into an array using the ARRAY_AGG function. You can combine arrays using functions like ARRAY_CONCAT (), and convert arrays to strings using ARRAY_TO_STRING ().
Using Thanks to Rajat Rawat for suggesting this solution. To find intersection of 2 sorted arrays, follow the below approach : 2) If arr1 [i] is smaller than arr2 [j] then increment i. 3) If arr1 [i] is greater than arr2 [j] then increment j. 4) If both are same then print any of them and increment both i and j.
You can use INTERSECT DISTINCT
-- build example table
WITH example as (
SELECT
* FROM UNNEST([
STRUCT([1,2] as col1, [2,3] as col2),
STRUCT([4,4,6],[6,7])
])
)
-- INTERSECT per row on two arrays
SELECT
ARRAY(SELECT * FROM example.col1
INTERSECT DISTINCT
(SELECT * FROM example.col2)
) AS result
FROM example
Sorry, I solved by myself:
#standardSQL
CREATE TEMPORARY FUNCTION intersection(x ARRAY<INT64>, y ARRAY<INT64>)
RETURNS INT64
LANGUAGE js AS """
var res = x.filter(value => -1 !== y.indexOf(value));
return res;
;
""";
Any other smarter idea is welcome! Thanks.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With