Linked Questions

43
votes
4answers
31k 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 ...
10
votes
1answer
30k 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 ...
6
votes
1answer
13k 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: ...
8
votes
1answer
13k 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 ...
9
votes
2answers
6k views

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
5
votes
2answers
5k 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 ...
6
votes
3answers
5k 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 ...
3
votes
1answer
7k 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>"...
7
votes
2answers
1k views

How to apply ORDER BY and LIMIT in combination with an aggregate function?

A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685. I have a simple table layout: class person: belongs to a class I want to ...
2
votes
2answers
1k views

Call UPSERT function multiple times to different inputs

I have a function: merge_vehicles(vid, cid, vname, reg_no, name, name_1st) Can i call it multiple times on inputs like (2335, 55, '246BDH', '246BDH', '811', 1), (2336, 55, '038THX', '038THX', '831',...
1
vote
1answer
5k views

Create dynamic WHERE clause from a JSON field

We have tables like this: people_people ( "id" integer... "attributes" HSTORE ) object_permission ( "permissions" JSON ) An example of an object_permission.permissions is: {"matricule":{"...
2
votes
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 ...