Stack Overflow
Stack Overflow

Questions tagged [unnest]

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

456 questions
2
votes
2 answers

PostgreSQL: Select data from another table by elements of an array and then display in order

I have a table with a collection of parts. These parts have an id and a serial number provided by the manufacturer. Parts are put together to build an assembly and the order which the parts are assembled matters. When this assembly is made, a new…
tomsrobots
  • 307
  • 1
  • 3
  • 10
2
votes
2 answers

How to unnest a dictionary from XML in R?

I am attempting to convert this xml to a dataframe in R: https://d396qusza40orc.cloudfront.net/getdata%2Fdata%2Frestaurants.xml library(xml2) library(tidyverse) fileurl <-…
DataGwynn
  • 45
  • 5
2
votes
2 answers

Extracting JSON objects in Athena / Presto for dynamic keys

I have two string formatted columns in my table. One of the columns has json objects in it. The keys in the JSON objects are not fixed - so the problem is I cant use standard json extract functions. Here is how the table…
chuzzle
  • 33
  • 4
2
votes
1 answer

Return specific item from array on condition in flat structure output

I want to output a flat data table (for a csv) where I use the field predictionModels.modelID as the header and the corresponding value of predictionModels.predictions.passengers when predictionModels.predictions.daysAhead = 1. The number of models…
ciaran haines
  • 294
  • 1
  • 11
2
votes
1 answer

In PostgreSQL, how can I use each element in an array as a key in another table?

There is a SQL query as follows: Query A: SELECT user, color_id_array FROM relation; Result A: user, color_id_array john, [1, 2] bob, [2, 2, 2] amy, [2, 2, 3] Query B: SELECT inf_name, inf_code FROM infomation; Result…
nanana
  • 33
  • 4
2
votes
1 answer

Jooq resolves unnest / table to union all (Oracle)

I'm using jOOQ to delete a variable number of rows from an Oracle database: List ids = Lists.newArrayList(1, 2, 3, 4); db.deleteFrom(MESSAGE) .where(MESSAGE.ID.in(ids)) .execute(); However, this means that a variable number of…
xani
  • 784
  • 1
  • 7
  • 21
2
votes
1 answer

SQL: CROSS JOIN UNNEST and include data from rows with NULLs in CROSS JOIN UNNEST column

I'm looking for assistance in the below SQL query where column policy_array has NULL values in some rows, but arrays of policy data in others. I would like to be able to include data from rows even when policy_array is NULL in the output. When I…
Chris Saindon
  • 21
  • 2
2
votes
2 answers

How to unnest a dataframe resulting from a test in R

I want to unnest the res2dataframe but I got the following error: x[[1]] must be a vector, not a object. What should I do? library(tidyverse) df <- tibble(id = c(1,2,3,4,5,6,7,8), test = c(2,1,2,1,2,1,2,1), difference =…
sbac
  • 1,897
  • 1
  • 18
  • 31
2
votes
2 answers

Should I be using unnest_wider and rowMeans to get the average of a list column?

I have a simple data set. The row names are a meaningful index and column 1 has a list of values. What I eventually want is the average of that list for each row name. What it looks like now: row name years 108457 [1200, 1200, 1540,…
user3278
  • 23
  • 4
2
votes
1 answer

Presto unnest/map complex json

I've been on stack for a few hours exploring examples of other presto unnest/map/cast solutions but I can't seem to find one that works for my data. Here's a sample of my data: with test_data (id, messy_json) AS ( VALUES ('TEST_A', JSON…
Derrick Tsoi
  • 23
  • 4
2
votes
1 answer

Cannot cast to array (varchar) on Presto when unnesting a column

My data on the column _idcounts is like the following: 00A=10;500=20;500=3;00e=11;001(ta)=1; As I want to unnest this column I did my query as: SELECT t._idcounts, anotherField from myDataBase CROSS JOIN UNNEST( cast(_idcounts as array))…
Catarina Nogueira
  • 1,024
  • 2
  • 12
  • 28
2
votes
2 answers

How do I add column names to a select that is doing unnest struct in BigQuery

I am constructing a table using given values like this SELECT * FROM UNNEST ([ STRUCT (1, 3.07766, 6.31371, 12.7062, 63.65600), (2, 1.88562, 2.91999, 4.30265, 9.92482), (3, 1.63774, 2.35336, 3.18243, 5.84089) ]) which creates columns with names…
Andrii Kusch
  • 33
  • 4
2
votes
1 answer

How to join with unnest function in athena?

i have this query on Athena trip.id as tripid , segment.id as segmentid , segment.distance as mileage , segment.maxspeed as maxspeed , segment.duration as duration , segment.roadtype as roadtype , segment.timeslotdata as timeslots , extract( week…
Ares Bertelli
  • 67
  • 1
  • 5
2
votes
1 answer

AWS Athena query JSON array with AND Condition

I have JSON data like this saved in S3. I am using ATHENA to write select statements. { "sample_data":{ "people":[ { "firstName":"Emily", "address":{ "streetAddress":"101", …
Pradeep Charan
  • 653
  • 2
  • 7
  • 28
2
votes
3 answers

Google BigQuery: UNNEST where each different key becomes a column

I have this table with several columns containing dictionaries: payloadKV, metaKV, etc. I need to unnest the dict and pivot the result to put each key in a column and the value in the correspondent cell of that row,column. The desired output of the…
Iván Sánchez
  • 248
  • 1
  • 10
1 2 3
…
30 31