Questions tagged [array]
The array tag has no usage guidance.
316
questions
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/...
- 25.1k
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 ...
- 101
1
vote
1
answer
561
views
Remap array columns to single column with value
Here is the sample database schema.
| id | Critical | High | Low |
--------------------------------------------
| 1 | {apple, ball} | {cat} | {dog, egg} |
Now I want to remap these to ...
CommunityBot
- 1
-1
votes
1
answer
44
views
Replacing substrings in array elements
I have a table with an array of URLs in column urls. Now there's a need to update some URLs changing the hostname. E.g.:
{https://storage.host1.com/file.jpg, https://storage.host1.com/file2.jpg}
...
- 172k
0
votes
1
answer
44
views
Performance issues with @> operator in postgres 14
CREATE TABLE public.article (
id uuid NOT NULL,
tags _text NULL,
CONSTRAINT article_pkey PRIMARY KEY (id)
);
I have the requirement to load articles that have a tag which is in a large ...
- 172k
0
votes
1
answer
1k
views
Use ENUM in array of objects
Is it possible to setup an ARRAY column with objects/JSON on a TABLE, forcing a parameter of the objects in the ARRAY to adhere to an ENUM, while keeping that object parameter unique?
Data examples:
...
CommunityBot
- 1
0
votes
1
answer
723
views
postgres reverse jsonb array
Postgres 13
I have a table with jsonb array and I need to revert the array. Any idea on how I can do that?
CREATE OR REPLACE FUNCTION array_reverse(anyarray) RETURNS anyarray AS $$
SELECT ARRAY(
...
CommunityBot
- 1
25
votes
2
answers
82k
views
How to get particular object from jsonb array in PostgreSQL?
I have a field called 'user' that holds a json array that roughly looks like this:
"user":
[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]
Now I want a query like:
select count from ...
- 103
50
votes
2
answers
67k
views
Convert right side of join of many to many into array
When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row.
Example with 3 tables:...
- 172k
0
votes
1
answer
5
views
How can I get a linear forecast in BigQuery?
BigQuery has no native support for linear forecasting outside of machine learning, but it does support user-defined functions, and these can accept arrays as inputs. However, (as of 2023/10 anyway), ...
- 5,957
1
vote
1
answer
787
views
In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?
In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
CommunityBot
- 1
0
votes
2
answers
1k
views
How to case text array elements automatically?
According to PostgreSQL documentation on the ARRAY constructor:
By default, the array element type is the common type of the member
expressions, determined using the same rules as for UNION or CASE
...
- 101
8
votes
1
answer
23k
views
How to remove known elements from a JSON[] array in PostgreSQL?
I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
- 172k
0
votes
1
answer
432
views
Storing arrays of data in a time-series database
I'm building an low utilization time-series database to capture yearly data points for a set of items fewer than 100,000.
My question has to do with storing arrays of data in a way that is easily ...
CommunityBot
- 1
2
votes
1
answer
479
views
Creating a TYPE that is an Array within a Function (or a predefined Array TYPE) in PostgreSQL
I am researching migration of a major system from Oracle to PostgreSQL. Getting into coding functions now, and am looking to see if there is a compatible object in PostgreSQL where, dynamically ...
CommunityBot
- 1
1
vote
1
answer
505
views
Multi dimensional JSON Array SQL Query
I'm struggling to write an appropriate query for my data ->
"schools":[
{
"org_symbol":"School 1",
"criteria":[
[
...
CommunityBot
- 1
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....
CommunityBot
- 1
0
votes
1
answer
53
views
Convert PostgreSQL array type
Given an enum type (e.g. letter).
What is the syntax to convert an existing array to the enum type? The existing values are valid for the enum. For example, how would I convert letter_data in:
create ...
- 172k
0
votes
0
answers
31
views
Create index on array of composite types
I have this example
create type price_schedule_day as enum ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday');
create type price_element as
(
price_start ...
- 417
1
vote
1
answer
248
views
PostgreSQL - PostGIS ST_SetValues - int array cast to double and back
I am using ST_SetValues with array defined as ARRAY[[9, 9], [9, 9]]::double precision[][].
If I store 32bit integer value in this array can I retrieve this value exactly after casting from double to ...
CommunityBot
- 1
2
votes
1
answer
699
views
Filter by list of values pairs
I have Postgresql 9.4 database and products table. For each product there must be list of pairs:
price; quantity for which this price is acutal
Products table may contain millions and billions of ...
CommunityBot
- 1
6
votes
2
answers
11k
views
How does postgres store array values?
Postgres seems to do some sort of compression on array values. I have a table with 2.1 million rows, each has 2 columns of smallint arrays, all populated with 1440 values. most of these values are -...
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,...
34
votes
1
answer
75k
views
Query JSON array of obejcts against multiple values
I want to write a query against a jsonb type table-column in Postgres that, given an array of customers IDs, will find corresponding groups.
Given this example table:
CREATE TABLE grp(d jsonb NOT NULL)...
- 172k
11
votes
2
answers
36k
views
Find rows containing a key in a JSONB array of records
I'm trying to query for a key present in an array of objects. This structure:
column jdata
{"name": "Somedata",
"array": [ {"name":"bla1", "attr&...
- 172k
12
votes
3
answers
31k
views
How to unnest and GROUP BY elements of a JSON array?
Given the band table, with a json column holding an array :
id | people
---+-------------
1 | ['John', 'Thomas']
2 | ['John', 'James']
3 | ['James', 'George']
How to list the number of bands each ...
- 172k
2
votes
2
answers
2k
views
How to get lexemes in original sort order from ts_vector?
Postgres' full text search to_tsvector() function returns a sorted array of lexemes. I need exactly that, just with lexemes in original sort order. Is there any such Postgres function, or do I need to ...
CommunityBot
- 1
3
votes
1
answer
1k
views
Searching INET[] for CIDR match, efficiently using indexes (without UNNEST)
This question stems from having recently discovered that the following are very, very different in terms of performance
DROP TABLE IF EXISTS dns_lookup;
CREATE TABLE IF NOT EXISTS dns_lookup (
...
- 3,503
0
votes
1
answer
45
views
How to calculate quantity based on a boolean field and retrieve values from a JSON field in MySQL
I'm working with a table called price_options that has the following fields: id, slots, list_slots, and quantity.
Issue Description
My objective is to calculate the quantity for each price option ...
- 19.4k
134
votes
8
answers
431k
views
How to turn JSON array into Postgres array?
I have a column data of type json that holds JSON documents like this:
{
"name": "foo",
"tags": ["foo", "bar"]
}
I would like to turn the nested tags array into a concatenated string ('foo, ...
- 101
0
votes
1
answer
103
views
Snowflake - extracting country/ state names - RegEx or Array?
I have a bunch of sloppy Geo text in a field.
However the country/ state names or codes are relatively clean.
It'll say Poland 23 or Illinois Remote or Frogballs, Germany or TX, AL, AK.
I have a ...
- 1,651
3
votes
3
answers
11k
views
Is it possible to remove a JSON array element by value in MySQL?
In MySQL, it's possible to remove an array element by index:
SELECT JSON_REMOVE('["a", "b", "c"]', '$[1]') `result`;
+------------+
| result |
+------------+
| ["...
- 9
1
vote
1
answer
246
views
Find integer in array is very slow
PostgreSQL Version PostgreSQL 15.0, compiled by Visual C++ build 1914, 64-bit
This is my "victim" table.
create table if not exists "KeywordsStats" (
id bigserial primary key, &...
- 29.5k
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}...
- 99
1
vote
2
answers
216
views
How to update each value in an array with a function?
I want to trim spaces from each value in a text[].
If this was just a text column I could do:
update server set tags = rtrim(ltrim(tags));
However how do I do this for an array? As otherwise that ...
- 172k
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 ...
- 172k
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 ...
- 136
7
votes
1
answer
6k
views
What is the difference between POSTGRES _text and text[][] array types?
I'm using a postgres database created by a script (Osm2pgsql). It has created some fields with the type _text for two dimensional arrays of text. I'm trying to understand why it didn't just create a ...
- 2,063
10
votes
2
answers
9k
views
ORDER BY gives ERROR: function array_position(text[], character varying) does not exist
I have a pretty basic categorical column in a Postgres database that is currently stored as VARCHAR. I can select a count of each with:
I though adding an ORDER BY array_position() would do it:
...
- 101
0
votes
1
answer
2k
views
How to remove NULL values in multidimensional array?
SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid
,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
- 172k
4
votes
2
answers
3k
views
ERROR: data type text[] has no default operator class for access method "gist"
Whenever I try to try to create a GIST index on text[], I get the above error.
CREATE TABLE bar(f)
AS VALUES
(ARRAY['foo','bar','baz']);
CREATE INDEX ON bar USING gist(f);
ERROR: data type text[]...
- 111
0
votes
0
answers
176
views
Compare 2 arrays with duplicate values in PostgreSQL
I need to compare 2 arrays but with taking into account duplicate values counting.
Currently:
select array[1,2,3,3] <@ array[1,2,3]
=> true
select array[1,2,3,3] <@ array[1,2,3,3]
=> ...
- 1
1
vote
0
answers
141
views
PostgreSQL - updating a counter outside a loop function
PostgreSQL 14
Here is a function :
CREATE OR REPLACE FUNCTION test(datedebut integer)
returns text
LANGUAGE plpgsql AS
$$
DECLARE
tab integer[];
periodes text;
i integer;
BEGIN
tab := Array[[0,...
- 2,248
1
vote
1
answer
1k
views
Receiving error "can not access rows from a non-nested table item" while working with associative arrays
Below , I'm trying to use the data of the array I've declared,
in the where clause of the query but I receive the following error :
declare
v_input varchar2(400) := '0,1,2,3,4';
type ...
- 2,923
0
votes
1
answer
259
views
Receiving error "Local collection types not allowed" while working with associative arrays
I have an input parameter with this format '0,1,2,3,4' , what I want to do is to declare an array with a variable size (by this I mean the input parameter might be like '0,1' , so the size is not fix),...
- 40.4k
1
vote
0
answers
25
views
Best practice to store subitems
I am going to create my first Dynamodb database table. I have 3 options in my mind:
1. Create a long table where every subitem is a new row.
Example:
user John | item 1 | 2021
user John | item 2 | ...
- 111
0
votes
2
answers
281
views
PostgreSQL Multicolumn GIN Index with Ordering
I have a large table (~20M records) in a Postgres DB with a "tags" column of ARRAY type, and a "date_inserted" column. I want to be able to query the latest inserted records for a ...
- 38.6k
0
votes
1
answer
121
views
PostgreSQL: Return ENUM values like data_type modifiers
When querying the datatype using format_type(tttypid, atttypmod) from pg_attribute, I would like to return the ENUM values like atttypmod values.
attname | type
--------+-------------------------------...
CommunityBot
- 1
8
votes
7
answers
1k
views
How can I generate all trailing substrings following a delimeter?
Given a string that may contain multiple instances of a delimiter, I want to generate all substrings starting after that character.
For example, given a string like 'a.b.c.d.e' (or array {a,b,c,d,e}, ...
- 172k
0
votes
1
answer
687
views
How do you pass the array of composite type to the argument of an SQL function?
Database example: https://dbfiddle.uk/sERgZPiB
Tables and types
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
login text NOT NULL,
password text NOT NULL,
email ...
CommunityBot
- 1