将列和jsonb对象数组合并为一个jsonb列

Question

我有一个表visitors(id, email, first_seen, sessions, etc.)

以及存储定制的、用户提供的数据的另一个表CCD_。

我想查询这些，并将访问者数据列和跟踪合并到一个名为data 的列中

例如，假设我有两个跟踪

(id: 3, visitor_id: 1, field: "orders_made", value: 2)
(id: 4, visitor_id: 1, field: "city", value: 'new york')

和访问者

(id: 1, email: '[e-mail], sessions: 5)

我希望结果以的形式出现

(id: 1, data: {email: '[e-mail]', sessions: 5, orders_made: 2, city: 'new york'})

使用Postgres9.4最好的方法是什么？

Answer 1

我首先会说跟踪是个坏主意。如果您没有太多要跟踪的内容，只需存储json；这就是它的初衷。如果你有很多事情要跟踪，随着时间的推移，你会对跟踪的性能感到非常不满意。

首先，您需要一个来自trackings的json对象：

-- WARNING: Behavior of this with duplicate field names is undefined!
SELECT json_object(array_agg(field), array_agg(value)) FROM trackings WHERE ...

为访问者获取json相对容易：

SELECT row_to_json(email, sessions) FROM visitors WHERE ...;

我建议你不要把这些都挤在一起。如果您有一个名为电子邮件的字段，会发生什么？相反：

SELECT row_to_json((SELECT 
  (
SELECT row_to_json(email, sessions) FROM visitors WHERE ...
  ) AS visitor
  , (
SELECT json_object(array_agg(field), array_agg(value)) FROM trackings WHERE ...
  ) AS trackings
));