sql - 使用聚合函数更新 json 数组

Question

使用 Postgres 9.3、Python 2.7、psycopg2。
我有一个名为 SomeTable 的表，其中包含一个 json 字段 some_json_array 和 row_id 键。

some_json_array 看起来像这样:

"[{'key': 'value_one'}, {'key': 'value_two'}, etc]"

我还有一个函数，其中我试图将一些元素添加到与给定的 row_id 相对应的 SomeTable 的 json 数组中。

我的代码如下:

CREATE OR REPLACE FUNCTION add_elements (insertion_id smallint, new_elements_json json)
RETURNS void AS $$
BEGIN
    UPDATE SomeTable
    SET some_json_array = (SELECT array_to_json(array_agg(some_json_array) || array_agg(new_elements_json)))
    WHERE row_id = insertion_id;
END;
$$ LANGUAGE plpgsql;

我收到以下错误:

Cannot use aggregate function in UPDATE

我认为这是在提示 array_agg()。

我应该如何修改这个函数以使其工作？如有必要，我可以将 WHERE 子句粘贴到 SELECT 语句中。

Answer 1

聚合函数不能像普通函数一样使用，只能在聚合上下文中使用。

由于 Postgres 不能直接操作 JSON 数组(它只是一个有效的 json value 到 Postgres，而不是一个数组)你必须......

• 要么将 json 值转换为 text，连接新元素，生成有效的 JSON 语法并转换回来，
• 或转换为 json 的 Postgres 数组:json[]，附加新元素并将此 Postgres 数组转换回 json值(value)。
  array_to_json() 足够聪明，不会再对 Postgres 数组的 json 元素进行编码。

第一种方法更容易出错。您将不得不手动构建有效的 JSON。这是第二个变体的实现:

CREATE OR REPLACE FUNCTION add_elements (_id int2, _elem json)
  RETURNS void AS
$func$
BEGIN
    UPDATE SomeTable s
    SET    some_json_array = array_to_json(ARRAY(
              SELECT * FROM json_array_elements(s.some_json_array)
              UNION  ALL SELECT _elem
              ))
    WHERE  s.row_id = _id;
END
$func$ LANGUAGE plpgsql;

函数包装器大部分与问题无关。可以只是一个普通的 SQL 语句。

相关:

• How to turn json array into postgres array?