unnest is a function from the tidyr package that can expand the list columns.
Questions tagged [unnest]
456 questions
0
votes
2 answers
Combine elements of array into different array
I need to split text elements in an array and combine the elements (array_agg) by index into different rows
E.g., input is
'{cat$ball$x... , dog$bat$y...}'::text[]
I need to split each element by '$' and the desired output is:
{cat,dog} - row…
LJU
- 13
- 2
0
votes
2 answers
plpgsql cursor on unnest function
I have a plpgsql function like:
DO
$$
DECLARE
c_id c.id%TYPE;
j_text c.j_options%TYPE;
j_option varchar;
c1 c%ROWTYPE;
begin
CREATE TYPE my_row_type AS (c2 TEXT);
for
--c1 in select c.j_options, c.id from c c
c1 in select *…
user1107753
- 1,566
- 4
- 24
- 36
0
votes
3 answers
Splitting characters after regexp_split_to_array
select name, (regexp_split_to_array(name, '')) from table
The result is {a,b,c,d,e}. Is it possible to split that up to individual rows and group by name so that it looks like:
a 1
b 2
c 3
d 4
e 5
c3win90
- 69
- 3
- 11
0
votes
3 answers
Distinct SQL is not working with UNNEST and ARRAY
I have database called Data like following :
ID | Name | Weakness1 | Weakness2 | Weakness3
_____________________________________________
1 | A | W1 | W2 | Null
2 | B | NULL | W2 | W3
3 | C | W1 | …
Elbert
- 516
- 1
- 5
- 15
0
votes
1 answer
SQL: match set of words against another set and sort by count and prefix-ness of matches
I need to match a user input against a string column. Both contain space-separated words.
The ordering criteria is:
count of words matched from the beginning (prefix match) desc
count of words matched desc
columns where matched words come in the…
meandre
- 2,141
- 2
- 16
- 21
0
votes
4 answers
I want to count the number of occurences of a value in a string
Given a column namely a which is a result of array_to_string(array(some_column)), how do I count an occurrence of a value from it?
Say I have '1,2,3,3,4,5,6,3' as a value of a column.
How do I get the number of occurrences for the value '3'?
Kevin
- 327
- 6
- 17
0
votes
2 answers
Unnest arrays of different dimensions
Is there a function or query that could return arrays of different dimensions as a set? For example, I would like to return the values
ARRAY[1]
ARRAY[2,3]
ARRAY[4,5,6]
as
1
2
3
4
5
6
Nick
- 555
- 5
- 22
0
votes
1 answer
angularjs unnest ng-repeats into one query
{{tag}}
How can I unnest it into one query. I imagined it like this:
Andi Giga
- 3,744
- 9
- 38
- 68
0
votes
1 answer
How to obtain min or max of a 3-dimensional array?
How do I find the maximum (or minimum) element of a 3-dimensional array in PostgreSQL?
Is there any function or faster way instead of this?:
min:= A[1][1][1];
for i IN 1..9 LOOP
for j IN 1..9 LOOP
for k IN 1..9 LOOP
min :=…
elena afshar
- 45
- 4
0
votes
1 answer
Postgresql change column to row in a generic way
A key value pair(column name, value) needs to be retrieved from a table with about hundred columns.
The following query does exactly what is needed.
With dummy AS (
Select 1 as Col1, 2 as Col2
)
SELECT
unnest(array['Col1', 'Col2']) AS "Column…
Gopal
- 1,372
- 2
- 16
- 32
0
votes
1 answer
New array aggregate function
I have a table like this:
id | array
1 | {8,8,8,x,u,x,x}
2 | {8,8,8,x,8,x,x}
...|...
n | {8,u,u,x,u,x,x}
It contains time of work each employee (cols in array are days of week, u and x are days free of work).
Is it possible to aggregate…
Vexator
- 181
- 3
- 9
-1
votes
0 answers
Wants to convert IN clause to temporary table using unnest struct in SQL
I am trying to optimize DELETE query where I am searching almost 200-500 values in one go like below:
DELETE FROM trade WHERE (tradeId, sourceFlag) IN (('1321', true), ('2321321', false) );
I want to improve like below query but want to change…
Kammy
- 409
- 1
- 7
- 26
-1
votes
1 answer
BigQuery - Get fields of nested Repeated Records
I am working with BigQuery tables that can have many levels of nested repeated record fields, as shown in the example.
I need to make a select on the given table, extract only some fields and ignore others, and at the end have the same structure…
Boschi Francesco
- 43
- 4
-1
votes
1 answer
Unnest multiple columns and join only the sequential values from each column bigquery
I'm facing an issue that I cannot resolve. Is it possible to unnest array so, that each value from each list (column) joins only to its corresponding value (by the sequence) in another list? So first value in the first column only with the first…
KayEss
- 419
- 4
- 18
-1
votes
2 answers
Unnest vs just having every row needed in table
I have a choice in how a data table is created and am wondering which approach is more performant.
Making a table with a row for every data point,
Making a table with an array column that will allow repeated content to be unnested
That is, if I…
autonopy
- 429
- 8
- 12