postgresql - 如何返回对象的自定义数组并联接其他一些表？

我问了太多关于jsonb的问题，仍然有点迷茫。
我有以下表格：

CREATE TABLE _data (
  id       serial PRIMARY KEY
, data     jsonb
);

--with the following rows:
pk | data
---|------------------------
1  | {"year": 2012, "model": "honda"}
2  | {"year": 2014, "model": "toyota"}


CREATE TABLE _people (
  pk         serial PRIMARY KEY
  user       integer
, data       integer
, updated    timestamp without time zone
, documents  jsonb
);

-- with the following rows:
pk  | user   | data| updated                  | documents
----|--------|-----|--------------------------|---------------
1   | 1      | 1   | 2015-08-22 16:05:40.76   | [{"type": "spreadsheet", "title": "mySpreadsheet", "length": 1278, "ignoredKey": "ignoreme"}, {"type": "document", "title": "My Nice Title"}]
2   | 1      | 1   | 2015-08-24 16:03:00      | [{"type": "spreadsheet", "title": "anothersheet", "length": 1400, "ignoredKey": "ignoreme"}, {"type": "document", "title": "here's another document"}]



CREATE TABLE _users (
  pk       serial PRIMARY KEY
, name     text
);

-- with the following example row:

pk   | name
-----|------
1    | Jim Bob


I am trying to get the following output (notice I am ignoring some keys in my documents...specifically "ignoredKey":

User     | Data                             | Updated                   |Documents
---------|----------------------------------|---------------------------|------------
Jim Bob  | {"year": 2012, "model": "honda"} |2015-08-22 16:05:40.764122 | [{"type": "spreadsheet", "title": "mySpreadsheet", "length": 1278}, {"type": "document", "title": "My Nice Title"}]

Jim Bob  | {"year": 2014, "model": "toyota"} |2015-08-24 16:03:00        | [{"type": "spreadsheet", "title": "anothersheet", "length": 1400}, {"type": "document", "title": "here's another document"}]

我有：

SELECT p.pk, u.name, custom_docs, d.data FROM _people p,
  jsonb_to_recordset(p.documents) doc(type text, title text, length numeric)
  LEFT JOIN _data d ON p.data = d.pk
  LEFT JOIN _users u ON p.user = u.pk

这就给了我一个错误：

ERROR:  invalid reference to FROM-clause entry for table "p"
LINE 3:   LEFT JOIN _data d ON p.data = d.pk
                               ^
HINT:  There is an entry for table "p", but it cannot be referenced from this part of the query.

编辑1：
如下文所述，我需要“文档”列，该列创建为一个对象数组……形式如下：

[{"type": "spreadsheet", "title": "mySpreadsheet", "length": 1278}, {"type": "document", "title": "My Nice Title"}]

一旦检索到任何其他格式就很难使用。
编辑2：
Dmitry的回答帮助我更进一步，但是“Documents”列包含所有行中的所有文档，而不是我需要的那些：

WITH docs AS (
  SELECT array_agg(to_json(changed_structure)) as changed_json_array
  FROM _people p,jsonb_to_recordset(p.documents) AS changed_structure(type text, title text, length numeric)
)
SELECT u.name,d.data,p.updated,docs.changed_json_array FROM docs,_people p
LEFT JOIN _users u ON u.pk = p.user
LEFT JOIN _data d ON d.pk = p.data;

这给了我：

name    | data                              | updated                    | documents
------- | ----------------------------------| ---------------------------|
Jim Bob | {"year": 2012, "model": "honda"}  | 2015-08-22 16:05:40.764122 | {"{\"type\":\"spreadsheet\",\"title\":\"mySpreadsheet\",\"length\":1278}","{\"type\":\"document\",\"title\":\"My Nice Title\",\"length\":null}","{\"type\":\"spreadsheet\",\"title\":\"anothersheet\",\"length\":1400}","{\"type\":\"document\",\"title\":\"here's another document\",\"length\":null}"}
Jim Bob | {"year": 2014, "model": "toyota"} | 2015-08-24-16:03:00        | {"{\"type\":\"spreadsheet\",\"title\":\"mySpreadsheet\",\"length\":1278}","{\"type\":\"document\",\"title\":\"My Nice Title\",\"length\":null}","{\"type\":\"spreadsheet\",\"title\":\"anothersheet\",\"length\":1400}","{\"type\":\"document\",\"title\":\"here's another document\",\"length\":null}"}

最佳答案

这里的主要动机是使用json_agg(to_json(doc))fromjsonb_to_recordset(p.documents)：

select pk, json_agg(to_json(doc)) doc
from _people p, jsonb_to_recordset(p.documents) doc(type text, title text, length numeric)
group by 1

 pk |                                                                doc
----+-----------------------------------------------------------------------------------------------------------------------------------
  1 | [{"type":"spreadsheet","title":"mySpreadsheet","length":1278}, {"type":"document","title":"My Nice Title","length":null}]
  2 | [{"type":"spreadsheet","title":"anothersheet","length":1400}, {"type":"document","title":"heres another document","length":null}]
(2 rows)

并适当使用连接：

select u.name, d.data, p.updated, s.doc
from _people p
left join _users u on u.pk = p.auser
left join _data d on d.pk = p.data
left join (
    select pk, json_agg(to_json(doc)) doc
    from _people p, jsonb_to_recordset(p.documents) doc(type text, title text, length numeric)
    group by 1
    ) s on s.pk = p.pk

  name   |               data               |        updated         |                                                                doc
---------+----------------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------
 Jim Bob | {"year": 2012, "model": "honda"} | 2015-08-22 16:05:40.76 | [{"type":"spreadsheet","title":"mySpreadsheet","length":1278}, {"type":"document","title":"My Nice Title","length":null}]
 Jim Bob | {"year": 2012, "model": "honda"} | 2015-08-24 16:03:00    | [{"type":"spreadsheet","title":"anothersheet","length":1400}, {"type":"document","title":"heres another document","length":null}]
(2 rows)

注意：我必须将_users.user改为_users.auser。