Linked Questions
12 questions linked to/from Select columns inside json_agg
49
votes
2
answers
82k
views
Replace multiple columns with single JSON column
I am running PostgreSQL 9.3.4. I have a table with 3 columns:
id
name
addr
1
n1
ad1
2
n2
ad2
I need to move the data to a new table with a JSON column like:
id
data
1
{"name": "n1&...
- 1,807
6
votes
4
answers
29k
views
Return total number of rows and selected (aggregated) data
I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table.
How can I do that or how can I get the same result in ...
- 683
10
votes
1
answer
5k
views
Syntax shorthand for updating only changed rows in UPSERT
I have a query which upserts into Postgres when a key conflicts only if there are changes to one of the other columns (to avoid unnecessary inserts and returned rows where nothing was actually changed)...
- 203
5
votes
1
answer
6k
views
How to select sub-object with given keys from JSONB?
How to extract / get / select a "sub object" from a json(b) object in Postgres?
There seems to be a lot of info that get me almost all the way there but not quite. Lots of stuff about ...
- 153
7
votes
2
answers
720
views
Create smaller row from alias and preserve column names
Using Postgres:
SELECT users."name" AS "name"
, array_to_json(array_agg(sites)) as sites
FROM remodel.users AS users
JOIN remodel.user_sites AS user_sites
ON users.id=user_sites.user
JOIN ...
- 73
2
votes
1
answer
3k
views
Efficiently return two aggregated arrays from a m:n table
I have a table for a many-to-many relationship over a users table to represent a follow relationship between users:
CREATE TABLE users (
id text PRIMARY KEY,
username text NOT NULL
);
CREATE ...
- 193
0
votes
3
answers
3k
views
How can I return multiple columns from an array of ids?
I have a two tables like the following:
workshops table
+----+--------------+------------+
| id | name | option_ids |
+----+--------------+------------+
| 1 | Conversation | {2,3} |
+----...
3
votes
1
answer
6k
views
Why does json_agg fail with error "function json_agg(record) does not exist"?
With postgresql version 9.2, I'm trying to run a query that selects certain columns to build a json object. Here's the query, which I derived from this question:
select
a.id
, json_agg((SELECT x ...
- 137
3
votes
2
answers
1k
views
Filter and append data to row
My query and my data set (copied from sqlfiddle):
CREATE TABLE one (
id int4 primary key,
p_id int4,
k_id int4
);
CREATE TABLE two(
id int4 primary key,
p_id int4,
k_id int4,
...
- 683
0
votes
1
answer
3k
views
Convert right side of join of many to many into array of objects
I have 2 tables:
Table subjects: id, class_id, title, created_at, updated_at
Table marks: id, student_id, class_id, mark, created_at, updated_at, subject_id
I want to query subjects table and have it ...
- 3
0
votes
0
answers
694
views
to_json vs ::json in postgres
I was wondering how to address the tsrange problem in postgres, as tsrange('2010-01-01 14:30', '2010-01-01 15:30')::json returns annoying escapes: "[\"2010-01-01 14:30:00\",\"2010-...
- 888
0
votes
1
answer
361
views
Combining table under different alias
I have two tables trips and users. Whenever an user posts a trip, it stores id of the user as foreign key. And while fetching, I am joining those two tables the following way.
Query:
SELECT title, ...
- 103