Questions tagged [array]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 ...