Questions tagged [array]
The array tag has no usage guidance.
221
questions
0
votes
0answers
25 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-...
0
votes
0answers
15 views
How to build simple jsonb object out of multiple arrays in postgres?
If I have three (or more) Postgres arrays with the same number of elements, and I want to build a multi-level JSONB object out of them—how can I do this?
Example:
create temp table tbl1 as
select
...
0
votes
0answers
7 views
Google BigQuery - Using ARRAY_AGG and Aliasing Fields
I am aggregating various tables into a singular "mega" table to drive an analytics platform.
While I have several WITH clauses defined above my provided code snippet, these are simply to mitigate ...
5
votes
3answers
40 views
Allow for dynamic result sets from plpgsql function by filtering based on an array passed in?
I think I've avoided the XY problem here, as I'm laying out my solutions to the real underlying problem (summarizing multiple tables in a dynamic fashion) and I'm only asking about the one final part ...
0
votes
2answers
94 views
Mongodb Sort Array Objects within Array using expression
Please help to make query in mongodb.
I want mongo query in which sort array objects using some expression.
Example.
I have documents like
{
"_id:"...
1
vote
1answer
46 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 ...
0
votes
1answer
33 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:
{
...
0
votes
1answer
46 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 ...
0
votes
1answer
42 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 ...
0
votes
1answer
18 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 ...
0
votes
1answer
34 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 ...
2
votes
1answer
38 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 ...
5
votes
1answer
81 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,...
1
vote
1answer
29 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 ...
0
votes
1answer
22 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}...
0
votes
1answer
26 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
3answers
103 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 ...
0
votes
1answer
65 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
1answer
72 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":"...
2
votes
1answer
815 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
540 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
52 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
32 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
20 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
295 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 ...
0
votes
1answer
20 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
276 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
329 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
85 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
14 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
83 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
987 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
353 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
482 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 ...
2
votes
1answer
3k 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
2k 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
26 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
28 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
2k 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
257 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
102 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
1k 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
435 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
446 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
219 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 ...
2
votes
3answers
8k 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 ...
5
votes
2answers
7k 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
301 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
227 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
416 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 ...
