Postgres 9.4: Include sibling column in each jsonb array result

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:

name,office_id
John,1
Jane,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.

FuzzyTree · Accepted Answer · 2015-11-15 07:28:06Z

2

You can use json_array_elements to expand the json array

select id , json_array_elements(employees)->>'name' from mytable

http://sqlfiddle.com/#!15/9a847/5

answered Nov 15, 2015 at 7:28

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeremy Baker Over a year ago

In order to use that, do I need to specify each column independently. For example, if the objects in the array have 5 properties, do I need to use 5 json_array_elements calls?

FuzzyTree Over a year ago

@JeremyBaker yes, one json_array_element call per property that you need to select. The result will contain the same number of rows as the number of elements in the json array.

Collectives™ on Stack Overflow

Postgres 9.4: Include sibling column in each jsonb array result

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related