Linked Questions

73 votes
4 answers
57k views

Function Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
Derek Downey's user avatar
  • 23.5k
31 votes
4 answers
46k views

Unquoting JSON strings; print JSON strings without quotes

SELECT json_array_elements('["one", "two"]'::json) gives result | json_array_elements | | :------------------ | | "one" | | "two" | I would like to ...
Maxim Yefremov's user avatar
33 votes
2 answers
79k views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES ('{"parent":null,"children":[2,3]}'), ('{"parent":1, "children":[4,5]}'), ('{"parent":...
Kokizzu's user avatar
  • 1,363
19 votes
2 answers
20k views

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-...
Evan Carroll's user avatar
  • 63.1k
13 votes
4 answers
5k views

Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

I stumbled upon this question on a Twitter conversation with Lukas Eder. Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using ...
Vlad Mihalcea's user avatar
25 votes
2 answers
83k views

How to get particular object from jsonb array in PostgreSQL?

I have a field called 'user' that holds a json array that roughly looks like this: "user": [{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}] Now I want a query like: select count from ...
Rabi C Shah's user avatar
25 votes
1 answer
26k views

How to preserve the original order of elements in an unnested array?

Given the string: 'I think that PostgreSQL is nifty' I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details and ...
swasheck's user avatar
  • 10.7k
12 votes
3 answers
32k 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 | ['John', 'Thomas'] 2 | ['John', 'James'] 3 | ['James', 'George'] How to list the number of bands each ...
Bax's user avatar
  • 303
12 votes
1 answer
42k views

Is it possible to select distinct values in a json document using PostgreSQL?

I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents { id: 1, s: ...
Trasplazio Garzuglio's user avatar
18 votes
1 answer
9k views

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

On PG 14's documentation, in the CREATE FUNCTION section, the manual says that the body of a LANGUAGE SQL function can either be a single statement: RETURN expression or a block: BEGIN ATOMIC ...
ARX's user avatar
  • 1,419
13 votes
1 answer
16k views

Faster query with pattern-matching on multiple text fields

I have one Postgres table with more than 20M tuples: first_name | last_name | email ------------------------------------------- bat | man | [email protected] arya | vidal | foo@...
Victor's user avatar
  • 233
8 votes
1 answer
24k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
spa's user avatar
  • 203
10 votes
1 answer
13k 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 ...
Vakarelov's user avatar
  • 103
7 votes
1 answer
24k views

How to remove object from json array?

My table: CREATE TABLE items ( id BIGINT PRIMARY KEY NOT NULL, name VARCHAR, images json ); images format: [ { "id": "owner", "full": "<url>", "thumb": "<url>"...
TSK's user avatar
  • 73
10 votes
2 answers
14k views

How to combine ORDER BY and LIMIT with an aggregate function?

Here is a fiddle for my question. I have a simple table layout: class person: belongs to a class I want to select all classes, and for each class, I want the first two person identifiers of the ...
Jarius Hebzo's user avatar

15 30 50 per page