I’ve been fighting this for a long time, but I am not able to construct a query, which would select (and preferably also order by) a count of items in an array. I do have table data like this: And what did I try… also But it sais I’ve tried a lot of different formats but coming from the MS
Tag: arrays
Aggregate rows according to JSON array content
I have a PSQL table with json tags, that are always strings stored in a json array : I would like to query, for instance, the count of entries in the table containing each tag. For instance, I’d like to get : I tried but if does not work, since it gives I guess I need to get the list
PostgreSQL Integer in Array is not merge-joinable
I would like to perform a FULL OUTER JOIN ON a condition that is an integer being in an array of integer. The query would look something like this: SELECT thing FROM table1 t1 FULL OUTER JOIN table2 …
postgres procedure list of values
I’m trying to pass in a list of values into a Postgres 11 procedure. The query has an IN condition. How do I pass in a list of values to the procedure & then how do I use it in the query? create …
How to iterate over PostgreSQL jsonb array of objects and modify elements?
Given jsonb array and PostgreSQL 12: Need to convert it to: Is it possible somehow to iterate over jsonb array and downcase only “type” values? I tried: but it separates data and type pairs into separateelements. Answer You need an intermediate level of nesting to rebuild the objects before you aggregate them in the array: for this, you can use
How to use LOWER() on elements of a jsonb column in PostgreSQL?
I have a PostgreSQL table like this one: Table t id | keys (jsonb) —+—————- 1 | [“Key1”, “Key2”] My goal is to query this table to find out if one of the keys of a …
Postgresql – Select in Select Alias Problem
I have query. https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1b3a39357a5fe028f57b9ac26d147a1d SELECT users.id as user_ids, (SELECT ARRAY_AGG(DISTINCT CONCAT(user_has_bonuses….
Split array(bigint) to multiple rows of bigint
I’m querying some data (SQL, presto), each item can be the parent or child of another. The parent IDs and the child IDs are stored in the row for that primary ID as an array(bigint). Each task could be the child of multiple parents. It looks something like: I want a list of all of the parent Ids and each
postgres – aggregate items in text array
I have an array of text items, each text item in the array is a delimited string, here is a sample: drop table if exists tmp; create table tmp(x text); insert into tmp select (‘1~41.5~50~1|2~43.72~…
PostgreSQL(function) – Implementation of where clause to get rows by a value(concatenate) in the JSONB column
I am trying to get the rows from a table where I want the condition to check for a value from a jsonb column. The column stores the data as: In the function, I check for the value using: but I want the 420 to be replaced with “ID” which I pass through the function. The only way I came