Postgres 9.4: Include sibling column in jsonb array on SELECT

Question

If I have a table like this:

office_id int
employees jsonb

and the data looks something like this:

1
[{ "name" : "John" }, { "name" : "Jane" }]

Is there an easy way to query so that the results look like this:

office_id,employees
1,[{ "name" : "John", "office_id" : 1 }, { "name" : "Jane", "office_id" : 1 }]

For example data, check out this sqlfiddle: http://sqlfiddle.com/#!15/ac37b/1/0

The results should actually look like this:

id  employees
1   [{ "name" : "John", "office_id" : 1 }, { "name" : "Jane", "office_id" : 1 }]
2   [{ "name" : "Jamal", "office_id" : 1 }]

I've been reading through the json functions and it seems like it's possible, but I can't seem to figure it out. I would rather not have to store the office_id on each nested object.

Note: This is similar to my other question about jsonb arrays, but the desired output is different.

Can you provide clearer sample data? CREATE TABLE and INSERT statements? Right now this just looks like SELECT office_id, employees FROM my_table — Craig Ringer
– Craig Ringer, Commented Nov 16, 2015 at 4:26

zxq9 · Accepted Answer · 2015-11-16 04:20:21Z

I'm not sure if you are selecting from a Postgres table or a json object table. Doing a normal query and converting it to json can be done with json_agg().

Here is a normal query:

ao_db=# SELECT * FROM record.instance;
                  id                  | created_by |          created_on           | modified_by |          modified_on          
--------------------------------------+------------+-------------------------------+-------------+-------------------------------
 18d8ca56-87b6-11e5-9c15-48d22415d991 | sysop      | 2015-11-10 23:19:47.181026+09 | sysop       | 2015-11-10 23:19:47.181026+09
 190a0e86-87b6-11e5-9c15-48d22415d991 | sysop      | 2015-11-10 23:19:47.56517+09  | sysop       | 2015-11-10 23:19:47.56517+09
 57611c9c-87b6-11e5-8c4b-48d22415d991 | admin      | 2015-11-10 23:21:32.399775+09 | admin       | 2015-11-10 23:22:27.975266+09
(3 行)

Here is the same query passed through json_agg():

ao_db=# WITH j AS (SELECT * FROM record.instance) SELECT json_agg(j) FROM j;
                                                                                           json_agg                                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"id":"18d8ca56-87b6-11e5-9c15-48d22415d991","created_by":"sysop","created_on":"2015-11-10T23:19:47.181026+09:00","modified_by":"sysop","modified_on":"2015-11-10T23:19:47.181026+09:00"}, +
  {"id":"190a0e86-87b6-11e5-9c15-48d22415d991","created_by":"sysop","created_on":"2015-11-10T23:19:47.56517+09:00","modified_by":"sysop","modified_on":"2015-11-10T23:19:47.56517+09:00"},   +
  {"id":"57611c9c-87b6-11e5-8c4b-48d22415d991","created_by":"admin","created_on":"2015-11-10T23:21:32.399775+09:00","modified_by":"admin","modified_on":"2015-11-10T23:22:27.975266+09:00"}]

Collectives™ on Stack Overflow

Postgres 9.4: Include sibling column in jsonb array on SELECT

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related