Questions tagged [array]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
15 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":"...
1
vote
1answer
27 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,...
1
vote
1answer
48 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" }, { ...
0
votes
1answer
46 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
1answer
31 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
1answer
13 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 ...
0
votes
1answer
51 views

postgres: 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 ...
0
votes
1answer
16 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 ...
0
votes
0answers
76 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 ...
1
vote
2answers
41 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 ...
1
vote
2answers
57 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 ...
0
votes
0answers
11 views

How to always select records in array or empty array

I have a field records of jsonb[] type and I need to insert records from other tables(has many relation) whenever there is an update via trigger. When I apply select SELECT INTO records * FROM "...
1
vote
2answers
25 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....
0
votes
2answers
233 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, ...
0
votes
1answer
50 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 ...
1
vote
1answer
256 views

Postgresql: 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 ...
1
vote
1answer
1k 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 ...
2
votes
2answers
819 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 ...
1
vote
1answer
25 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 ...
0
votes
1answer
26 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 ...
1
vote
1answer
284 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 ...
0
votes
1answer
66 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....
0
votes
1answer
75 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 ...
1
vote
1answer
471 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(...
0
votes
2answers
101 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 ---...
2
votes
3answers
197 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 ...
1
vote
1answer
106 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 ...
1
vote
3answers
2k 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 ...
2
votes
2answers
3k 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 ...
0
votes
1answer
163 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, ...
3
votes
4answers
147 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 ...
0
votes
1answer
272 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 ...
1
vote
1answer
30 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) = '...
1
vote
1answer
2k 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/...
1
vote
2answers
56 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} ...
2
votes
1answer
653 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 ...
0
votes
1answer
210 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"...
0
votes
1answer
46 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 |...
0
votes
1answer
61 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 ...
2
votes
0answers
111 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 ...
1
vote
1answer
23 views

extract different number of rows from multiple tables

I have a quick question. I'm looking for the concept behind this, not necessarily a ready-to-use code, but if it's not too complex, I don't mind a quick solution. I have an app that manages accounts. ...
1
vote
0answers
50 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 ...
2
votes
1answer
3k views

What is the fastest way to verify that a json[] array is not empty?

I want to verify that an json[] column the_array is not empty. For a normal array, I could check on: the_array != '{}' However, that does not work for a json[], as demonstrated below: select '{}'::...
3
votes
1answer
1k views

Use array_position() function to get the most frequent value for column from pg_stats

I'm trying to execute this simple query in order to check whether some value (1000) belongs to MCV list used by Postgres query optimizer: SELECT array_position(most_common_vals, 1000) FROM pg_stats ...
2
votes
1answer
338 views

How to remove duplicates from any array and preserve ordering in PostgreSQL?

I'm looking for a way to eliminate duplicates from a PostgreSQL array while preserving the ordering of its elements. What I currently have are the following functions: create function array_unique( ...
1
vote
2answers
7k views

POSTGRES declaring variable containing date array in functions

I am trying to declare a date array. I have tried this: DECLARE dateVal DATE[] := ['2018-01-01','2018-02-01']; I get: ERROR: syntax error at or near "[" How do I properly declare it?
6
votes
3answers
4k 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 | ['George', 'John'] 2 | ['Thomas', 'James'] 3 | ['James', 'John'] How to list the number of bands each ...
0
votes
0answers
632 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 ...
1
vote
1answer
204 views

Have several 128 elements lists, need to obtain distance between them

Is storing them in a CUBE type field and then getting euclidean distance, (using <->) the only way? I can not save them as a list of floats, since i get: "can't adapt type numpyndarray" Can ...
2
votes
2answers
113 views

Testing if an element in a PostgreSQL array?

Is there an operator in PostgreSQL to test whether an element is in an array? Currently, I'm doing such tests in a slightly complex way by first constructing a singleton array for the element and ...