Stack Overflow
Stack Overflow

Questions tagged [unnest]

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

456 questions
-1
votes
3 answers

How can I unnest a repeated record in BigQuery, with one array giving the column names and another giving column values?

I am working in Google BigQuery and have a dataset with two repeated records: one is the element name and the other is the element value. A simplified version of my dataset (with just 2 entries) looks like…
Kevin Lucas
  • 39
  • 5
-1
votes
3 answers

How to query to Fetch Data of each Name in List for a Date Range

I have a table like Name Total Date A 10 2020-12-01 B 5 2020-12-01 A 10 2020-12-02 B 15 2020-12-02 now I have a List of Name and Date List Like @NameList = '[A],[B],[C],[D]' @DateList = …
kundan kumar
  • 1
-1
votes
1 answer

Using OFFSET instead of UNNEST for nested fields in Google Bigquery

A quick question to GBQ gurus. Here are two queries that are identical in their purpose first SELECT fullVisitorId AS userid, CONCAT(fullVisitorId, visitStartTime) AS session, visitStartTime + (hits[ OFFSET(0)].time / 1000) AS eventtime, …
Dmitri Ilin
  • 87
  • 4
  • 13
-1
votes
3 answers

Unnesting array of object in JS

Nested Array [ { "id": 16, "created_at": "2020-07-01T14:09:14.066Z", "file": { "name": "Annotation 2020-04-08 135240.jpg" } }, { "id": 15, …
Juber Nunes
  • 3
  • 1
  • 4
-1
votes
2 answers

UNNEST array and assign to new columns with CASE WHEN

I have following BigQuery table, which has nested structure, i.e. example below is one record in my table. Id | Date | Time | Code AQ5ME | 120520 | 0950 | 123 ---------- | 150520 | 1530 | 456 My goal is to unnest the array to achieve the following…
markoo
  • 19
  • 3
-1
votes
1 answer

PostgreSQL : Cannot convert JSON to row result

I have the JSOn data in a table of PostgreSQL database. { "city": "NY", "country": "US", "sales": [145, 222, 122, 124, 172], "dates": ["13.05.2020", "2.05.2020", "21.05.2020", "30.04.2020", "29.04.2020"] } I want to convert these…
user5871859
-1
votes
1 answer

Duplicating data when reading JSON structure using BigQuery

I see that data is getting duplicated or missing the sequence ad creating invalid data for the JSON Structure I have. Sample InPut Data: have two columns job_id and "conv_column" Which has below JSON structure. [ { "Source": "", "Number":…
Nanda
  • 159
  • 1
  • 13
-1
votes
1 answer

How do I unnest a list inside an R dataframe keeping both keys and values?

I'm currently working to extract data in JSON format into an R dataframe. The data provided is of the following format: Sample code to create test data: test_input_data <- data.frame(date.x=c("2017-08-17", "2017-07-26", "2017-10-04"),…
Hamza Juzer
  • 65
  • 6
-1
votes
1 answer

BigQuery: GA pageviews and events split by custom dimensions, content groups, and event category/action?

I am trying to create a view with the following fields: Date, values for custom dimension X, values for custom dimension Y, visitor ID, pageviews, values for content group A, values for content group B, event category, event action. I am aware this…
Jurij
  • 33
  • 5
-1
votes
2 answers

How to Left Join from an un-nested table?

I had written a code to create more records from 1 record based on some conditions. For example: If the user purchased a monthly package, the table will create records for each week until the Date Ended. From this: Order ID|Date Purchased| Date…
Shang Rong
  • 77
  • 1
  • 1
  • 6
-1
votes
2 answers

Edit nested list in python 3

How do I unnest a nested list or flatten a nested list. so that, servers = [["10.10.10.10" , "20.20.20.20"] ,["30.30.30.30"] , ["40.40.40.40", "50.50.50.50"] , ["60.60.60.60"],["70.70.70.70"]] becomes, servers = ["10.10.10.10" , "20.20.20.20"]…
degixer
  • 39
  • 10
-2
votes
2 answers

How to split these multiple rows in SQL?

I am currently studying SQL and I am still a newbie. I have this task where I need to split some rows with various entries like dates and user IDs. I really need help +-------+------------------------------+---------------------------+ | TYPE | …
Ace
  • 1
  • 1
-2
votes
1 answer

How to fix GENERATE_ARRAY() produced too many elements BigQuery

select * , row_number() OVER(PARTITION BY user_id,event_datetime_start,event_datetime_end ORDER BY user_id, event_datetime_start, event_datetime_end,dt_watched) rk from `blackout_tv_july` a cross join…
Jutarut Junchaiyapoom
  • 37
  • 1
  • 5
-2
votes
1 answer

Find All Combinations of Size 2 of array in BigQuery

I have a repeated value on Bigquery, example: {"a": 1, "b": 4, "c": 6, "d": 8} From this, I want every possible combination of two. So the output I'd be looking for is {"aa", "ab", "ac", "ad", "ba", "bb", "bc", "bd", "da", "db", "dc", "dd"}. The…
T A
  • 1
  • 2
-2
votes
1 answer

Duplicate rows where a column contains ","

I have data as below name subject a m1 b m2 c m1, m3 d m4, m5, m6 e m7 I would like to duplicate entire row where column subject contains , . The original data has many more columns than shown here. I will duplicate…
user2543622
  • 5,760
  • 25
  • 91
  • 159
1 2 3
…
30
31