Postgres 9.4:在每个jsonb数组结果中包括同级列 [英] Postgres 9.4: Include sibling column in each jsonb array result

问题描述

如果我有一个这样的表:

If I have a table like this:

office_id int
employees jsonb

数据看起来像这样:

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

我一直在阅读 json函数和似乎有可能，但我似乎无法弄清楚.我宁愿不必在每个嵌套对象上存储office_id.

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.

推荐答案

您可以使用json_array_elements扩展json数组

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

这篇关于Postgres 9.4:在每个jsonb数组结果中包括同级列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！