107 questions
1
vote
2
answers
53
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
166
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
32
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
117
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
96
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
95
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', ‘[
...
- 17.3k
0
votes
2
answers
408
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
308
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
289
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 ...
- 180k
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", ...
- 180k
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
450
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
954
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
66
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 ...
- 5