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

Question

我在 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:德米特里的回答帮助我更进一步，但“文档”列包含所有行的所有文档，而不是我需要的文档:

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

Answer 1

这里的主要动机是使用 jsonb_to_recordset(p.documents) 中的 json_agg(to_json(doc)):

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。