Questions tagged [array]
The array tag has no usage guidance.
316
questions
3
votes
1
answer
18k
views
How to check if a value is in an array in a table
I have a table if key - array pairs and need to check if a string is an element in a given array in this table. I have managed to write an if statement to check if the element is in an array but if I ...
- 175
0
votes
1
answer
4k
views
Filter based on values after join operation - MongoDB
I have two collections in the following format -
collection1:
{
"_id": "col1id1",
"name": "col1doc1",
"properties": [ "<_id1>", "<_id2>", "<_id3>"]
}
collection2:
{
...
- 111
0
votes
1
answer
552
views
Adding a subquery to my SELECT clause returns an error
I have a query which was originally written to retrieve work-event data based on whether the work-event was updated during a specified date range. The query ends up being used to populate a reporting ...
- 11
0
votes
1
answer
2k
views
Is possible to insert an array inside another array in postgresql?
Let's say I have a table in postgresql called 'Test' which contains a single column called 'Column1' of the type int[]. So, it can contain an array of integers for each row, for example, {1,2,3}. I ...
- 47
0
votes
1
answer
613
views
Search one array element with another
I have got tables:
children_tab
id
name
lastname
parent_tab
id
name
lastname
children_parent_tab
children_id
parent_id
There is also parent view that return in field children_ids elements from ...
- 13
0
votes
1
answer
692
views
How to optimize query for text array in PostgreSQL using `@>` operator
I have a column of type text[] and I want to search over this column using this SQL operator @>
Note: @> operator, it filters the data as such ['a','b'] in ['a','b','c']
The ...
- 159
5
votes
1
answer
2k
views
Constrain array values to an allowed set of values
I would like to set up a CHECK constraint that makes sure that a text[] column's elements contain only certain values.
Setting up an example:
CREATE TABLE foo(
countries text[]
);
INSERT INTO foo ...
- 557
5
votes
1
answer
448
views
Grouping by overlapping arrays, transitively, without duplicates
I've found:
Group by array overlapping
https://stackoverflow.com/a/23650080/5419599
However, I'm having trouble putting it to use in my case.
I have a table like so (the real myid values are hashes,...
- 588
1
vote
1
answer
63
views
Posgres Row Security Policy - Why Array faster than Bitstring?
I have a question:
I have two row security policies to restrict the access to a table.
The first policy uses an Array-Column "rights".
The "rights" column is type 16-bit bit/integer array and each ...
- 11
1
vote
2
answers
2k
views
Group by unique items in an array column and count records that contain each item
I have a table that looks like this:
records
-------------------------------------
id items[]
-------------------------------------
1 {toaster,piano}
2 {car}
3 {car, toaster}
4 {tv}...
- 111
0
votes
1
answer
173
views
Cumulative array aggregation in Postgres
Does anybody know if it is possible perform cumulative array aggregation in Postgres? For example, to transform this:
create table records
(
data text,
path text[]
);
insert into records values
(...
0
votes
3
answers
505
views
Array containment queries in Postgres
Question on GIN index on array.
I have 2 million rows in a work table.And I need to find work that a user can do, based on the skills he has and also the highest priority work. User always will have ...
- 103
0
votes
1
answer
2k
views
Accessing nested array in json blob in PostgreSQL
I have a query I need to run that accesses a doubly nested array inside a json blob. The data is structure like the following:
{
"id" : "5",
"data" : "[[1,2,3],[2,3,4],[4,5,6]....]
}
My query needs ...
0
votes
2
answers
4k
views
PostgreSQL: Find row with JSON object attribute in array
I have a postgres database which is set up like this:
CREATE TABLE arr_test (
id serial primary key,
data jsonb
);
INSERT INTO arr_test (data) VALUES
('{"test_row": 1, "my_arr": [{"serial":"...
- 3
12
votes
2
answers
12k
views
Combining array_agg and unnest
Given a dataset (with a GIN index on values):
key | values
-------------
1 | {4,2,1}
1 | {2,5}
2 | {4,1,3}
I want to aggregate the arrays:
key | values
-------------
1 | {4,2,1,5}
2 | {4,...
- 296
2
votes
1
answer
6k
views
flatten JSON array into comma delimited list
I am trying to construct a SELECT statement which will take a JSONB column in the following format:
{
"Cities": [
{
"Name": "Atlanta"
},
{
"Name": "London"
},
{
...
- 361
0
votes
1
answer
864
views
PostgreSQL 10 Optimizing slow query performance with aggregate function
I have two tables person and tag.
person has an bigint[] column containing ids of tags.
I want to write a query which fetches all tags including the count of the tagged persons.
So far I have the ...
0
votes
1
answer
234
views
Unstructed data field: Query all values from array of objects by key
I have a table that has a unique ID, and a second 'collumn' named 'data' that contains simple key/value items like:
"nickname": "value"
"fullName": "value"
"office": "value"
"unity": "value"
and a ...
0
votes
1
answer
709
views
Join on unnested integer array column and count occurrences in array
I am trying to join two tables after I have unnested one of them. The tables are:
CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60));
CREATE TABLE training_modules (id ...
- 15
0
votes
1
answer
1k
views
Using unnest in multiple rows
I have an array column containing information like
{a,b,c,a}
{a,b,a,b}
{b,a,a,b}
I want to run unnest on this array column, get the occurence of of each character.
a 2
b 1
c 1
a 2
b 2
b 2
a 2
I ...
- 1
0
votes
1
answer
151
views
SQLite return sum of duplicate rows based on array
If I have a simple table fruits that looks like this:
+---+---------+------+
| | name | lbs |
+---+---------+------+
| 1 | apple | 0.25 |
| 2 | melon | 3.00 |
+---+---------+------+
In a ...
- 103
0
votes
0
answers
4k
views
How to pass array of UUIDs *OR* empty array and return implicit (inner join)?
How would I pass a list that is either full of UUIDs or empty, then return only joined records with those id values or all records if empty?
This question (Casting an array of texts to an array of ...
- 101
5
votes
2
answers
5k
views
uniqueness in array column type throughout all records
There is a table
id serial primary key,
names varchar(20)[]
it contains such record
1, {"one", "two", "three"}
Then, next record is inserting (notice one array element already exists in existing ...
- 207
1
vote
2
answers
4k
views
Removing values from array in postgreSQL
Lets say I have a table called users, who looks something like the following:
users table:
|user_id | name |
1 Bob
2 Alice
3 Joe
4 Tom
And ...
- 341
1
vote
2
answers
1k
views
How can I find all rows that have at least one element in an array matching a pattern? [duplicate]
Consider the following example:
CREATE TEMPORARY TABLE testing (
names TEXT[]
);
INSERT INTO testing VALUES
('{toplevel1}'),
('{toplevel1.sub1, toplevel2.sub2}'),
('{toplevel1.sub1....
- 731
0
votes
2
answers
23k
views
How to group multiple ROW as ARRAY
This is my current Query:
SELECT innings_no,
Player_Name,
sum(NoBalls) as NB
FROM (
SELECT A.innings_no,
A.bowler,
...
- 101
2
votes
1
answer
12k
views
Using postgres to select a filtered array
I'm looking to select data from a table that contains an ID, and an array of values. But in the query results, i want to filter the array and remove values that overlap with a white list.
For ...
- 23
7
votes
2
answers
2k
views
How to store high-dimensional (N > 100) vectors and index for fast lookup by cosine similarity?
I am trying to store vectors for word/doc embeddings in a PostgreSQL table, and want to be able to quickly pull the N rows with highest cosine similarity to a given query vector. The vectors I'm ...
- 379
10
votes
1
answer
13k
views
Extract JSON array of numbers from JSON array of objects
I have a table containing a json array of objects similar to this:
id | record
____________________
name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...]
I would like to get a table ...
- 103
2
votes
2
answers
9k
views
Get the max value from a slice of a JSON Array?
I would like to get the max value within a slice of a Json object (typicaly [1,2,3,5,6,7,9,10]) which is contained in a field named Data of the table raw.
The limits Start & End of the slice are ...
- 127
1
vote
1
answer
388
views
Better way to select a matching subset of rows?
DB Fiddle link. I have a many to many table like this:
CREATE TABLE house_to_cats (
id SERIAL PRIMARY KEY,
house_id INTEGER,
cat_id INTEGER
);
-- house with cats 1 and 2: too small
INSERT INTO ...
- 669
0
votes
1
answer
157
views
Removing duplicate-ish paths generated from recursive CTE
I have some multi-parented data, that I run a recursive CTE against to find common ancestors:
item_id |item_name |path_name |id_path_array |
--------|----------|-------------|--------------|
1 ...
- 105
26
votes
4
answers
54k
views
How can I get a unique array in PostgreSQL?
The following
SELECT ARRAY[a,b,c,d]
FROM ( VALUES
('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);
Returns {foo,bar,foo,baz} of type text[]. I would like to get {foo,bar,baz} of type text[] with one ...
- 62.1k
1
vote
1
answer
1k
views
Mongodb what is the cost of array queries?
As far as I can tell, find queries in mongodb involving an array appear very expensive. Take as example the queries given in: https://docs.mongodb.com/manual/tutorial/query-array-of-documents/
db....
- 415
0
votes
1
answer
653
views
How do I modify a JSON object?
I have a table "raw_data" in which I have a field "data" containing a json array like:
{'a' : 1,
'b' : 2,
'c' : 3}
I would like two different things:
Append a new item like {'d' : 4} at the end of ...
- 127
2
votes
1
answer
6k
views
How to pass an array to a plpgsql function with VARIADIC parameter
I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.
I have already defined the function
CREATE OR REPLACE FUNCTION mix_table_fields(...
- 301
0
votes
2
answers
4k
views
PostgreSQL - how to generate an array containing column name and column data...?
I'd like to create a dictionary - an array - from a simple query, to include column names in output.
IE, I'd like to turn this:
SELECT id, last, first FROM names;
id | last | first
---...
- 111
3
votes
3
answers
3k
views
Pass array of mixed type into stored FUNCTION
I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction.
the problem here is, that one argument is list of n ...
- 151
1
vote
1
answer
1k
views
How to prepend an element to a JSON array?
I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty).
How ...
- 167
3
votes
3
answers
48k
views
How to select multiple values into an array and loop through? (postgres 9.3)
I have simple table for the sake of argument. I have a function that selects ids and loops through them called loop_test. I can select an array of ids and loop through them, causing my changes in a ...
16
votes
4
answers
49k
views
Casting an array of texts to an array of UUIDs
How can I cast an array of texts into an array of UUIDs?
I need to do a join between two tables: users and projects.
The users table has an array field named project_ids containing the project IDs ...
- 371
1
vote
3
answers
3k
views
Use an array returned from a subquery as argument in WHERE clause with ANY function in outer query
A query like this:
SELECT *
FROM person
WHERE person.favorite_number = ANY(
ARRAY[ 2 , 3 ]
)
;
…runs. The ANY() function tells us if the single value is found within an array of values. So, ...
- 10.6k
3
votes
4
answers
1k
views
Representing lists in SQL
My database setup is modern (My)SQL.
I've got a Content Table, including ids.
Now, users should be able to comment on content.
To achieve this, arrays have come to my mind.
Now I see several options ...
- 143
0
votes
1
answer
2k
views
Index for split string. PostgreSQL 10
I have the following string in db:
A/B/C/
A/B/D/
E/F/C/
It's the uri column.
The table is the following:
create table meta_info (
id bigserial primary key,
uri varchar,
created ...
- 115
1
vote
1
answer
1k
views
How can I index a text[] field in postgresql?
I have two columns:
alternate_names text[]
first_name character varying
I am not sure how I can write an index on this type of query?
SELECT "people".* FROM "people"
WHERE (lower(first_name) = '...
- 231
2
votes
2
answers
14k
views
How can you insert data into a ARRAY column on a Snowflake table using SQL?
I am having difficultly finding documentation on how to insert data into an ARRAY column type using SQL on a Snowflake table.
Snowflake Documentation: https://docs.snowflake.net/manuals/sql-reference/...
- 461
1
vote
3
answers
2k
views
How do You concatenate a value to an array that was retrieved through a query in sql?
I have a table named "mkvtable" that establishes a correspondence between individual strings and arrays of strings:
word | mkvword
---------+-----------------
hello | {world,friend}
...
- 78
3
votes
1
answer
3k
views
Error while inserting into array of timestamps
Im using postgres 10.1 and the Datestyle config is the following
DateStyle
-----------
ISO, MDY
Im having this error
ERROR: date/time field value out of range: "1535673858" Hint: Perhaps
you ...
- 775
-1
votes
1
answer
2k
views
Postgresql JSONB Column, how to query for multiple specific elements in a JSON array
Giving the following JSONB data structure of a stopInfo column in a myTable table:
{
"stopInfo": [
{
"stopType": "Origin",
"partnerId": "KR01",
"locationId"...
- 361
1
vote
1
answer
102
views
What is the best way to manage interdependent indexes
So I have a table of items as so
|firstIndex|subIndex|relatedIndexes|
-----------------------------------
| 1 | 1 | 2 ,6 , 8 |
-----------------------------------
| 1 | 2 |...
- 143