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

我在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}"}