Stack Overflow
Stack Overflow

Questions tagged [unnest]

unnest is a function from the tidyr package that can expand the list columns.

456 questions
27
votes
1 answer

Google BigQuery, I lost null row when using 'unnest' function

#StandardSQL WITH tableA AS ( SELECT ["T001", "T002", "T003"] AS T_id, [1, 5] AS L_id UNION ALL SELECT ["T008", "T009"] AS T_id, NULL AS L_id ) SELECT * FROM tableA, UNNEST(L_id) AS unnest When I executed this code, I expected the result such as…
柳沼慎哉
  • 295
  • 1
  • 3
  • 5
16
votes
4 answers

Find rows where text array contains value similar to input

I'm trying to get rows where a column of type text[] contains a value similar to some user input. What I've thought and done so far is to use the 'ANY' and 'LIKE' operator like this: select * from someTable where '%someInput%' LIKE…
Jose Hermosilla Rodrigo
  • 3,513
  • 6
  • 22
  • 38
15
votes
1 answer

Unnesting in SQL (Athena): How to convert array of structs into an array of values plucked from the structs?

I am taking samples from a Bayesian statistical model, serializing them with Avro, uploading them to S3, and querying them with Athena. I need help writing a query that unnests an array in the table. The CREATE TABLE query looks like: CREATE…
Count Zero
  • 630
  • 1
  • 6
  • 15
14
votes
3 answers

Postgres JOIN with unnest

Assume I have following tables: table: followers_arrays id | array --------+--------- 1 | {3,4,5} table: small_profiles id | username | pic --------+----------+------- 3 | aaaa | abcd 4 | bbbb | abcd …
Rafal Wiliński
  • 2,240
  • 1
  • 21
  • 26
10
votes
4 answers

Any more concise `data.table` way to unnest a nested column in data.table?

Assuming we have a data.table with a nested column val dt <- data.table( grp = c(1, 2, 1, 3, 4), val = list("a", c("b", "c"), c("d", "e", "f"), "g", c("h", "i")) ) which shows as > dt grp val 1: 1 a 2: 2 b,c 3: 1 d,e,f 4: 3 …
ThomasIsCoding
  • 96,636
  • 9
  • 24
  • 81
10
votes
2 answers

using tidyr unnest with NULL values

I converted a JSON file into a data.frame with a a nested list structure, which I would like to unnest and flatten. Some of the values in the list are NULL, which unnest does not accept. If I replace the NULL values with a data.frame structure that…
Michiel van Dijk
  • 157
  • 1
  • 9
9
votes
1 answer

Column definitions in ROWS FROM() with multiple unnest calls

I would like to use multiple arrays within a select clause. The obvious one didn't work and postgresql points to ROWS FROM() ... select * from unnest(array[1,2], array[3,4]) as (a int, b int); ERROR: UNNEST() with multiple arguments cannot have a…
hooblei
  • 3,210
  • 2
  • 20
  • 17
9
votes
1 answer

PostgreSQL multidimensional arrays

I'm trying to pass data around as a multidimensional array, and I'm getting behavior that seems odd to me. Specifically I'm trying to get a single element out of a 2 dimensional array (so a 1 dimensional array out of my 2 dimension array), and it…
user3742898
  • 343
  • 1
  • 2
  • 9
9
votes
2 answers

Using UNNEST with a JOIN

I want to be able to use unnest() function in PostgreSQL in a complicated SQL query that has many JOINs. Here's the example query: SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id FROM mentions INNER JOIN…
İlker İnanç
  • 607
  • 3
  • 8
  • 16
8
votes
3 answers

Unnest or unchop dataframe containing lists of different lengths

I have a dataframe with several columns containing list columns that I want to unnest (or unchop). BUT, they are different lengths, so the resulting error is Error: No common size for... Here is a reprex to show what works and doesn't…
Megan Beckett
  • 337
  • 1
  • 6
8
votes
2 answers

Postgresql - Opposite of string_agg

I'm looking for a postgresql function that will do the opposite of string_agg. I have a movies table where the tags column contains values such as Action|Adventure|Drama|Horror|Sci-Fi Action|Horror|Sci-Fi I would like to get a distinct list of…
Superdooperhero
  • 7,584
  • 19
  • 83
  • 138
8
votes
1 answer

Oracle's analogue for unnest array from PostgreSQL

I have table with subnetworks like cidr | ip And I want to select subnetworks by list of belonging ips. In postgres I can do it in this way: select ips.ip, net.uid, net.cidr from TBL_SID_SUBNETWORK net, (select unnest(ARRAY[1,2,3])…
dkiselev
  • 890
  • 8
  • 20
8
votes
2 answers

Parallel unnest() and sort order in PostgreSQL

I understand that using SELECT unnest(ARRAY[5,3,9]) as id without an ORDER BY clause, the order of the result set is not guaranteed. I could for example get: id -- 3 5 9 But what about the following request: SELECT unnest(ARRAY[5,3,9]) as id, …
Jerome WAGNER
  • 21,986
  • 8
  • 62
  • 77
8
votes
1 answer

SQL multiple UNNEST in single select list

I was implementing a Query system. I implemented unnest function. Now user was asking about using multiple unnest in a single select statement. I was using PostgreSQL as kind of guideline since most users was using it before our query…
user1192878
  • 704
  • 1
  • 10
  • 20
7
votes
1 answer

Nested list to dataframe using tidyverse: faster than tidyr unnest_wider

I have a nested list from reading a JSON that stores logging info from a video game. The time element of the list is a simple vector, while inputManagerStates and syncedProperties are lists that may contain 0 or more elements. This is a follow-up on…
Claudiu Papasteri
  • 2,469
  • 1
  • 17
  • 30
1
2 3
…
30 31