Questions tagged [array]

The tag has no usage guidance.

43 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5 votes
0 answers
11k views

How to change array_agg() to return empty array on empty input in PostgreSQL?

I am trying to write a PostgreSQL array_agg() variant that returns the empty array '{}' instead of NULL. Trying to avoid the performance hit of custom aggregate functions with CREATE AGGRGATE, I tried ...
tinlyx's user avatar
  • 3,425
4 votes
0 answers
2k views

Does PostgreSQL support `unnest` as a column?

Does PostgreSQL document and support this behavior of the SRF unnest() as a computer/virtual column, SELECT unnest(array_agg(id)) AS id FROM foo GROUP BY CUBE (a,b,c,d) HAVING count(*) > 1 If ...
Evan Carroll's user avatar
  • 62.1k
3 votes
0 answers
1k views

Best way to cast array (text[]) to record-type?

PostgreSQL has the ability to create record types. A lot of operations like regexp_match return text[]. What is the most efficent way to go from text[] to a custom record type? Currently, I'm doing ...
Evan Carroll's user avatar
  • 62.1k
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 ...
user210170's user avatar
2 votes
0 answers
136 views

Copy to file and transform arrays

I'm copying data from a Postgres DB on AWS RDS to a Postgres-like DB on AWS Redshift. Varchar arrays are being exported as {foo,bar} but Redshift wants them as ["foo","bar"]. Redshift doesn't ...
Dennis's user avatar
  • 121
2 votes
0 answers
2k views

PostgreSQL: Storing large volume of photos and attached tags: many-to-many relationship or array of text?

This is my first post here and I am quite new to PostgreSQL - many apologies if my question may appear a bit unclear. We set up a database for storing a large volume of photo-metadata with attached ...
Alex's user avatar
  • 403
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 ...
Moisizz's user avatar
  • 21
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,...
Leehan's user avatar
  • 207
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 | ...
L777's user avatar
  • 111
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, &...
glmn's user avatar
  • 11
1 vote
1 answer
503 views

Multi dimensional JSON Array SQL Query

I'm struggling to write an appropriate query for my data -> "schools":[ { "org_symbol":"School 1", "criteria":[ [ ...
chris's user avatar
  • 11
1 vote
0 answers
98 views

Storing Image Filenames As An Array So I Can Add New Values Without Having To Re-Write The Entire Array - MySQL

I have an image library where I would like to create the functionality where users can create boards of selected images. The boards will essentially be collections of images on the site along a ...
pjk_ok's user avatar
  • 111
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 ...
Qi luo's user avatar
  • 25
1 vote
1 answer
560 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 ...
PaxPrz's user avatar
  • 219
1 vote
0 answers
591 views

Postgres Unnest or flatten a nested array inside of JSONB

I've got a table MY_TABLE that has the following columns: User Name Data Purchased Data is of the form : [{ "Id": 1, "Attributes": [\"[\\\"[\\\\\\\"[\\\\\\\\\\\\\\\"[\...
blazerix's user avatar
  • 111
1 vote
0 answers
58 views

Can I assign an index to an exploded array?

I'm doing a LATERAL EXPLODE on an array in Hive, is there a way of reliably assigning a row number based on the array element? It looks like calling row_number() on the results of the LATERAL EXPLODE ...
PhilHibbs's user avatar
  • 539
1 vote
0 answers
566 views

automatic cast of anonymous record array type to the array type of a named composite type in PostgreSQL?

From PostgreSQL documentation, it is possible to cast an anonymous record type to a named composite type at a function call site. Quoting the example there: CREATE TABLE mytable(f1 int, f2 float, f3 ...
tinlyx's user avatar
  • 3,425
1 vote
0 answers
680 views

Nested data in postgres

I have a CSV with columns of various data types, including what i would interpret as nested data. The nested data comes in many forms. For example: [13,11,23,18,18,21,23,16,8,20,22,18,20,17,13,10,8,...
matthewmturner's user avatar
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....
Daniel Valland's user avatar
1 vote
0 answers
71 views

Postgresql 10 arrays equality failure

I am using AWS RDS postgres instance version 10.3. Completly stuck with the problem. I have a table like: create table social_message( social_profile_id int8 not null, body text, subject ...
den bardadym's user avatar
1 vote
0 answers
2k views

Convert JSON array to native integer array

My function is returning json data which is containing two integer array. Now I need to get this two integer array list and passed it to another function. CREATE OR REPLACE FUNCTION method1(id ...
Somil's user avatar
  • 125
1 vote
0 answers
1k views

Table structure to store a an array of tags per entry

Lets say one wants to create a DB to store items, each having a Set<String> tags, associated with it. A straightforward way would be a flat table with ItemID, ItemName, DateCreated,... and a Set&...
Stepan's user avatar
  • 213
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 ...
Martin Perry's user avatar
1 vote
0 answers
633 views

Inner join on array columns from 3 tables

I have 2 tables that are related between them using integer data which is stored in arrays. Data table telemetria_data.historico_354677050164513 CREATE TABLE historico_354677050164513 ( imei ...
Gonzalo Vasquez's user avatar
1 vote
0 answers
382 views

Schema for ACL/User/Group/Role management

I'm building an app with some fairly complex permission and ACL rules and want to get my schema right. These are my goals: A user signs up on behalf on an organization. Organization has groups. ...
skyronic's user avatar
  • 111
1 vote
0 answers
99 views

ANY in postgresql gives weird error

when I do: SELECT ARRAY[4] I get array integer[] ------ {4} and if I do: select * from b where b.ID = ANY (array [4]) That works. Now, something else: select array_agg(4) I get: ...
aayushdagra's user avatar
1 vote
0 answers
259 views

Is there are way to use Fine Grain Auditing to record Varray bind values?

I have an application, and would like to use Oracle's FGA (Fine Grained-Auditing) to record varray bind information. Specifically, I am using Oracle Spatial and need to know at what extent users are ...
travis's user avatar
  • 111
1 vote
0 answers
2k views

storing arrays or images in a database table

I'm a beginner with both database design and use of forums such as this one so forgive me if I forget to provide some relevant information below. Basically I'm trying to store a set of arrays, in ...
Joost vdW's user avatar
1 vote
0 answers
607 views

Checking of one specific element of one array is in another array?

Suppose I have two arrays. One looking like (3, 9, 10, 39), and one looking like (4, 10, 9, 48, 391, 2). How would I (through pure MySQL) check if one of the elements in the first array is inside the ...
Mathias Lykkegaard Lorenzen's user avatar
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), ...
Jon of All Trades's user avatar
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 ...
mslot's user avatar
  • 417
0 votes
1 answer
102 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 ...
user45867's user avatar
  • 1,651
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] => ...
Matteo's user avatar
  • 1
0 votes
1 answer
722 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( ...
Daniel Kniaz's user avatar
0 votes
0 answers
201 views

Multiple tokens per account

In the context of user account management, I'm using Firebase. So for each login, the front-end sends an Auth ID, a FCM token (for sending notifications to the user), and the usual user details. Each ...
ahron's user avatar
  • 779
0 votes
0 answers
2k views

MariaDB: SQL query to return array of json objects and remove `\` from response

I am creating a sql query for api response in javascript. But i stuck on one part that is creating a array of json objects of same group. I have checked with the json_arrayagg and also ...
Krunal's user avatar
  • 1
0 votes
0 answers
300 views

Extract Image URLs from html text using Postgres sql Query

I have a column in postgres db in which html contain stored. Like this <p style="text-align: justify;"><strong><img alt="" src="https://images.unsplash.com/photo-1579121477063-...
5a01d01P's user avatar
  • 135
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 ...
Jhonatan Cruz's user avatar
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 ...
user3.1415927's user avatar
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 ...
a coder's user avatar
  • 208
0 votes
0 answers
871 views

MySQL version of split_to_array or split_to_table?

PostgreSQL supports two String Functions, that are incredibly useful regexp_split_to_array regexp_split_to_table Both of these functions essentially take two strings, (a) a pattern and (b) an input ...
Evan Carroll's user avatar
  • 62.1k
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: ...
David Gustavsson's user avatar
-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"...
daniel9x's user avatar
  • 361