107 questions
1
vote
2
answers
66
views
Why aren't my Django Postgres `ArrayAgg` members sorting?
I'm exploring the use of ArrayAgg and I don't understand why 'histidine-[13C6,15N3]' doesn't occur before 'isoleucine-[13C6,15N1]' in this example:
In [25]: for i in Infusate.objects.annotate(tns=...
- 1,225
0
votes
1
answer
201
views
snowflake ARRAY_AGG, replacing empty [] array by null
I am using this function ARRAY_AGG in snowflake sql.
It is giving me [] empty array many times as I have null values there. It is only giving it when i am using case when inside.
how can i get just a ...
- 382
-4
votes
1
answer
33
views
row_number could not be used in array_to_string(array_to_agg function
I've emp table which contains name & name2 field.
I need the output in a single line.
As i dont have unique id(emp_id) in this table, i am getting error as below :-
ERROR: aggregate function ...
1
vote
1
answer
126
views
Improve performance of query with inner join, operation between tables, and array_agg
I am using DuckDB, which should follows postgresql's syntax for the most part.
I have two tables t_cons (~200k) and t_flows (~1.2M).
For a row of t_cons there are many t_flows that match asset, year, ...
2
votes
2
answers
108
views
Can I batch Snowflake json output into separate rows based on LIMIT/OFFSET
I have a query in Snowflake that produces json output in rows. 29,000+ rows. I am using this query to create records in an MDM system. However, this ingestion process is a bit inefficient and costly. ...
- 25
3
votes
1
answer
104
views
Why array_agg() doesn't consider empty json column value?
Below is the postgres table table1:
CREATE TABLE table1 (
id INT PRIMARY KEY,
name TEXT,
skills JSON
);
with below 3 rows inserted:
INSERT INTO table1 (id, name, skills) VALUES
(1, 'Alice', ‘[
...
0
votes
2
answers
424
views
SQL Select Query for Roles/Permissions of a User
I have a database design with multiple many-to-many relationships, and am trying to write a SELECT statement that retrieves a result set with ONE ROW PER USER. To do this, I'd like to aggregate the ...
- 111
0
votes
1
answer
317
views
Snowflake query to create json - multiple results for single attribute
I have a table in snowflake that has three different columns being used as name type/values. These need to be mapped in an MDM tool using json. Of course, I cannot use the same attribute name more ...
- 25
0
votes
2
answers
296
views
How to return multiple values from Subquery as an Array using Django ORM
For example I have this model:
class Parent(models.Model):
name = models.CharField(max_length=255)
class Child(models.Model):
parent = models.ForeignKey(Parent, on_delete=models.CASCADE)
...
- 95
2
votes
1
answer
2k
views
Snowflake - ARRAY_AGG with LIMIT
We can use ARRAY_AGG aggregate function to generate an array from data.
The goal is to find a way to limit the input to specific number of entries like ARRAY_AGG(...) WITHIN GROUP(... LIMIT 3) without ...
- 181k
4
votes
1
answer
1k
views
Snowflake: ARRAY_AGG preserving "NULL"s
ARRAY_AGG function omits NULL by definition.
The requirement is to generate array with the following format:
SELECT [NULL, 'a1', 'a2', 'a3', NULL]
-- [ undefined, "a1", "a2", ...
- 181k
1
vote
1
answer
67
views
Combine data of 2 tables with same array struct in Bigquery
I need to combine the data of 2 tables, both has the same structure
Table 1:
key A.a A.b
1 2 2
3 3
4 4
2 1 1
table 2 :
key A.a A.b
1 5 5
6 6
...
- 11
-1
votes
1
answer
451
views
Redshift - Array returning single data per record
I have a table containing the following fields:
email - logged user email
allowed_id - A ID of another User
The table contains multiple entries for the same email, each one containing a different ...
- 481
1
vote
2
answers
968
views
In BigQuery how to concatenate arrays with distinct values in nested aggregations?
I have a BigQuery SQL below. The source table is aggregated from another table and field x contains distinct values. To further aggregate on field name, the query unnests x and uses array_agg(distinct ...
- 329
0
votes
1
answer
72
views
Appending data to existing string_agg in bigquery
when a person pings or calls, we should be able to show that data and if he doesn't do, it should show as null but when he makes multiple pings or calls, it is appending as multiple array.
below is ...
- 3
0
votes
2
answers
514
views
How to query other tables in ARRAY_AGG rows?
I have the below tables:
team
col_name
type
id
string
name
string
coach_id
string
player
col_name
type
id
string
name
string
team_id
string
main_skill_id
string
coach
col_name
type
id
string
name
...
- 3,823
1
vote
1
answer
1k
views
How can I use the "ARRAY_AGG" function twice in sequence in BigQuery? One inside the other?
I'm trying to make a query in BigQuery with the nested ARRAY_AGG function (one inside the other), as follows:
ARRAY_AGG(
STRUCT(
.
.
.
.
.
ARRAY_AGG(
...
0
votes
1
answer
204
views
How to count array elements in subquery using ARRAY_AGG in Postgresql?
The initial query finds the languages applied for each feature_id and makes the result into an array. I want to count the number of languages in each area, per feature_id, per version (new and old).
...
- 13
0
votes
1
answer
47
views
Filtering site with specific tags in Django while keeping all site tags aggregated in annotation field
Let's say I have the following django model:
class Tag(models.Model):
key = models.CharField(max_length=64, unique=True)
class Site(models.Model):
key = models.CharField(max_length=64, ...
- 11.6k
0
votes
1
answer
58
views
SQL Bigquery - Create a data set from 3 tables with master records and no duplicates with ARRAY_AGG(Struct)
I am trying to query 3 tables to create one data set with master item data, transaction data and then serial numbers/locations.
My left table is a unique key for each item mentioned once, then the ...
1
vote
1
answer
261
views
Remove duplicates from array_agg, where elements are also arrays
In a Postgres 11 database, I've got two arrays in two views which are joined to MAIN table:
vw_ideas_role_direction_mappings.user_direction_id - array (like {101,103,} or {101,103} or {101,} or {101,,,...
- 309
0
votes
0
answers
360
views
How to use Array_agg without returning the same values in different Order?
When using Array_agg, it returns the same values in different orders. I tried using distinct in a few places and it didn't work. I tried using an order before and after the array and it would fail or ...
- 13
0
votes
1
answer
1k
views
Postgres - using `ARRAY_AGG()` aggregate function in multiple cases for same array
I am trying to write an SQL query using CASE where I want to return the values from the campaign table of the selected row when on two cases:
The current time is between the start date and end date, ...
- 61
1
vote
2
answers
4k
views
Why do I have "message": "Expected 1 bindings, saw 0" when using array_agg with knex?
I'm using postgres and I try to make my request with array_agg in knex, but I have the error:
"message": "Expected 1 bindings, saw 0"
Does anyone know where this error may come ...
- 11
0
votes
2
answers
2k
views
Aggregate multiple columns into array
Suppose I have a table like the following
user
score_1
score_2
score_3
1
100
80
100
1
80
null
80
2
95
90
65
I would like to aggregate the 3 scores columns into an array. The result will look like (the ...
- 249
2
votes
3
answers
2k
views
Is it wasteful to use ARRAY_AGG to get the first non-NULL value in a column?
I use PostgreSQL 14 to manage a table which stores updates to a table of medics: users can update the first name, last name, and or/ the age of the medic. A field which was not touched by an update ...
- 95.2k
0
votes
0
answers
541
views
Athena aggregate calculated rows to an array
I have the following SQL that produces a list of values:
SELECT x / 100e0 FROM UNNEST(sequence(1, 100, 1)) t(x)
#
_col0
1
0.01
2
0.02
3
0.03
...
...
100
1
I'm trying to aggregate the results to get an ...
- 8,953
1
vote
1
answer
2k
views
Postgres, aggregate function and ORDER BY
The statement works for me:
SELECT e.id, e.title, array_agg(d.start_date) date, array_agg(d.id) ids
FROM event e JOIN event_date d ON e.id = d.event_id
GROUP BY e.id
I receive the results
id
title
...
- 593
-1
votes
1
answer
667
views
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 ...
3
votes
2
answers
2k
views
Multiple array_agg in sqlalchemy
I am working with postgres. I want to fetch multiple fields using array_agg in sqlalchemy. But I couldn't find examples of such use anywhere. I made my request. But I can't process the result of ...
0
votes
0
answers
47
views
Selecting name from aggregated ID in Postgresql
I have no clue how to do this.
(Select Dane1.Name, Array_agg(Dane1.UpperCat) as "upper_category", count(Dane1.UpperCat) As data_count, dane1.room_id
From
(Select
...
- 13
0
votes
1
answer
371
views
Open, high, low, close aggregation in BigQuery
Based on the BigQuery best practice of using ARRAY_AGG() to obtain the latest record, here's how I'm getting the first, last minimum and maximum values of a field for a day. The data is reported ...
- 3,010
0
votes
1
answer
331
views
BigQuery query optimisation - Unnest field to REPEATED STRUCT
I currently have the below query which perfectly works, but I would like to know if it can be optimized (perhaps avoid to UNNEST firstly and GROUP BY secondly and make transformations in one step).
...
- 57
-1
votes
1
answer
2k
views
How to dedup array_agg in bigquery
I created a new table with repeating records with duplicates.
I am trying to find the most efficient way to deduplicate records as this will be run
on a table with millions of records.
If you using ...
0
votes
1
answer
133
views
How to create array in postgres based on unique values from the same table joined twice?
I have a query which in I joined twice the same table, i.e:
left join tag tag1 [...]
left join tag tag2 [...]
I would like to have query with one column called tag which in I will have unique values ...
- 179
1
vote
1
answer
34
views
Why it works different with ~~ any()?
Let me first show you my table:
INSERT INTO my_table(name, brand, source)
VALUES ('Abc', 'Abc', 'Orig'),
('Auchan', 'Auchan', 'Orig'),
('Auchan', 'Auchan', 'Added'),
('dj-auchan-djd', 'Auchan', '...
0
votes
1
answer
604
views
How to get the results of conditions in array_agg into one line using postgres?
I need to process this table, divide the data into columns based on the value in exception_type in postgres.
service_id, date, exception_type
...
"2:11:CIST-100385-1-2023",2020-12-24,"2&...
- 3
0
votes
1
answer
444
views
Javascript - Unable to read json key values - Unexpected token i in JSON at position 1 - sql query output problem
I'ved got this string generated from sql query (basically using ARRAY_AGG and some cases to format the output) that looks like this
{id:2,name_of_division:'super department1',attendance_radius:1000}
...
- 3,391
0
votes
1
answer
179
views
How to avoid an array uf NULLS when making nested table in BQ
I am joining 2 tables, the join is left, and second table does not have all keys from the first, so that does produce NULLs on the right. How do I avoid them when grouping this into an array?
For ...
- 404
-1
votes
1
answer
875
views
Is it possible to use star expression inside array_agg(struct expressions in BigQuery?
I am joining several tables into one nested, and I was wondering if I could use a star expression, for example,
SELECT t1.*, ARRAY_AGG(STRUCT(t2.*)) FROM t1 JOIN t2 on t1.col1=t2.col1 GROUP BY t1.*
I ...
- 404
0
votes
1
answer
627
views
Bigquery - Repeated field inside of a repeated record
I have a denormalised table and I want to simplify it using repeated fields inside of BigQuery.
To illustrate what I am trying to do, I want to go from:
|CustomerNumber|InvoiceNumber|InvoiceLineItem|
|...
0
votes
0
answers
178
views
In Big Query, is there a way to take the latest row of data while building a struct over a window?
Hi and thanks for your time
I'm trying to run a process that backfills data between the partition dates of '2021-06-08' and '2021-06-10'.
Each partition requires 10 days of data
I want to produce a ...
- 31
2
votes
0
answers
66
views
mysql jsonarrayagg for comma seperated id
i have two table casetemp and medicication_master
patient has fields
id
name
age
sex
medicineid
1
xyz
23
M
1,2
2
abc
20
f
3
medicine has fields
id
medname
desc
1
crosin
fever tab
2
etzaa
shampoo
3
...
user15330211
2
votes
2
answers
2k
views
Big Query (SQL) convert multiple columns to rows / array
I have a data source with multiple similar columns that looks like this, with each question as a new column and the corresponding response:
Original
and I would like to convert it to use an array with ...
- 23
0
votes
2
answers
1k
views
How to get an empty array in array_agg if condition is not met?
I have 2 tables:
collections_books (collection_id, book_id)
users_collections (user_id, collection_id, access)
I'm using PostgreSQL.
The following query gives me a list of book_ids grouped by ...
- 122
0
votes
2
answers
225
views
SQL query with array of counts?
I have a table which looks like this:
record no firstType secondtype win?
1 X A 1
2 X A 0
3 X B 1
4 ...
4
votes
1
answer
6k
views
Django ArrayAgg - filtering annotated array
class Language(models.Model):
iso_code = models.CharField()
class Publisher(models.Model)
name = models.CharField()
class Book(modle.Model):
name = models.CharField()
language = ...
- 81
1
vote
2
answers
1k
views
How to object in row_to_json and array_agg from multiple value
I manage to create a jsonb output, however I need to make it as object
My current code
create function extract_data()
returns jsonb
as
$$
declare
v_msgar text[];
v_result jsonb;
...
- 23
0
votes
1
answer
943
views
Array_agg() being cast to a string on select
How can i force a type in db:select(db::raw($query)) on PHP??
I have a really complex query that I'm getting data from, to do so I'm using DB::raw($query).
I'm using the postgreSQL array_agg() ...
3
votes
1
answer
3k
views
Postgres GROUP BY an array column
I have a list of students and parents and would like to group them into families using the student id's. Parents who share common student id's can be considered to be a family while also students who ...
- 546