arrays - 以列值作为键聚合 JSON 数组

Question

我有一个场景，我试图聚合数据并将聚合的数据插入到另一个表中，所有这些都是从一个函数内部进行的。数据以数组和 JSON 的形式插入到另一个表中。我已经能够很好地聚合到数组中，但是我在尝试以我想要的方式将数据聚合到 JSON 中时遇到了一些麻烦。

基本上，这是我正在聚合的数据示例:

 id_1 | id_2 | cat_ids_array
------+------+---------------
  201 | 4232 | {9,10,11,13}
  201 | 4236 | {11}
  201 | 4249 | {12}
  201 | 4251 | {9,10}
  202 | 4245 | {11}
  202 | 4249 | {12}
  202 | 4251 | {9,10}
  202 | 4259 | {9}
  203 | 4232 | {9,10,11,13}
  203 | 4236 | {11}
  203 | 4249 | {12}
  203 | 4251 | {9,10}
  203 | 4377 | {14}
  204 | 4232 | {15,108}
  204 | 4236 | {15}
  205 | 4232 | {17,109}
  205 | 4245 | {17}
  205 | 4377 | {18}
  206 | 4253 | {20}

当我使用 json_agg() 将 id_2 和 cat_ids_array 聚合成一个 JSON 字符串时，我得到的是:

 id_1 |             json_agg             
------+----------------------------------
  201 | [{"f1":4232,"f2":[9,10,11,13]}, +
      |  {"f1":4236,"f2":[11]},         +
      |  {"f1":4249,"f2":[12]},         +
      |  {"f1":4251,"f2":[9,10]}]
  202 | [{"f1":4245,"f2":[11]},         +
      |  {"f1":4249,"f2":[12]},         +
      |  {"f1":4251,"f2":[9,10]},       +
      |  {"f1":4259,"f2":[9]}]
  203 | [{"f1":4232,"f2":[9,10,11,13]}, +
      |  {"f1":4236,"f2":[11]},         +
      |  {"f1":4249,"f2":[12]},         +
      |  {"f1":4251,"f2":[9,10]}        +
      |  {"f1":4377,"f2":[14]}]
  204 | [{"f1":4232,"f2":[15,108]},     +
      |  {"f1":4236,"f2":[15]}]
  205 | [{"f1":4232,"f2":[17,109]},     +
      |  {"f1":4245,"f2":[17]},         +
      |  {"f1":4377,"f2":[18]}]
  206 | [{"f1":4253,"f2":[20]}]

这是我想要得到的:

 id_1 | json_agg
------+-------------------------------------------------------------
  201 | [{"4232":[9,10,11,13],"4236":[11],"4249":[12],"4251":[9,10]}]
  202 | [{"4245":[11],"4249":[12],"4251":[9,10],"4259":[9]}]
  203 | [{"4232":[9,10,11,13],"4236":[11],"4249":[12],"4251":[9,10],"4377":[14]}]
  204 | [{"4232":[15,108],"4236":[15]}]
  205 | [{"4232":[17,109],"4245":[17],"4377":[18]}]
  206 | [{"4253":[20]}]

我想我必须进行某种字符串连接，但我不完全确定最好的方法。如前所述，我是在函数内部执行此操作的，因此我可以灵活地执行操作，因为我不仅限于 SELECT 语法魔术。

另外，我正在运行 PostgreSQL 9.3.4，并且在不久的将来无法升级到 9.4。

Answer 1

遗憾的是你不能升级，Postgres 9.4 有jsonb 和很多新增的JSON 功能。特别是json_build_object()非常适合您:

• Return multiple columns of the same row as JSON array of objects

差不多，但不完全是

虽然坚持使用 Postgres 9.3，但您可以从 hstore 获得帮助，以构建一个 hstore 值，其中 id_2 为键，cat_ids_array 作为值:

hstore(id_2::text, cat_ids_array::text)

或者:

hstore(id_2::text, array_to_json(cat_ids_array)::text)

然后:

json_agg(hstore(id_2::text, array_to_json(cat_ids_array)::text))

但是数组不被识别为数组。一旦转换为 hstore，它就是 Postgres 的文本字符串。有 hstore_to_json_loose() , 但它只识别 boolean 和数字类型。

解决方案

所以我最终像您预测的那样进行了字符串操作。有多种方法可以构造 json 字符串。每一个都或多或少是快速/优雅的:

format('{"%s":[%s]}', id_2::text, translate(cat_ids_array::text, '{}',''))::json
format('{"%s":%s}', id_2::text, to_json(cat_ids_array))::json
replace(replace(to_json((id_2, cat_ids_array))::text, 'f1":',''),',"f2', '')::json

我选择了第二个变体，它似乎是最可靠的并且适用于除简单 int[] 之外的其他数组类型，这可能需要转义:

SELECT id_1
     , json_agg(format('{"%s":%s}', id_2::text, to_json(cat_ids_array))::json)
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

结果如愿。

SQL Fiddle展示所有。