使用aggregrate函数更新json数组 [英] UPDATE json array using aggregrate function

问题描述

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

Working with Postgres 9.3, Python 2.7, psycopg2.
I have a table called SomeTable with a json field some_json_array and row_id key.

some_json_array看起来像这样:

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

我还有一个函数，其中我尝试向与给定row_id相对应的SomeTable的json数组中添加一些元素.

I also have a function in which I'm trying to add some elements to the json array of SomeTable corresponding to the given row_id.

我的代码如下:

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()的

.

which I believe is complaining about array_agg().

我将如何修改此功能以使其起作用?如有必要，我可以将WHERE子句粘贴到SELECT语句中.

How would I modify this function to make it work? I can stick the WHERE clause into the SELECT statement if necessary.

推荐答案

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

Aggregate functions cannot be used like plain functions, only in an aggregate context.

由于Postgres无法直接操作JSON数组(它只是有效的 json value 到Postgres，而不是数组)，您必须...

Since Postgres cannot manipulate JSON arrays directly (it's just a valid json value to Postgres, not an array) you have to ...

• 要么将json值强制转换为text，然后连接新元素，生成有效的JSON语法并进行转换，
• 或转换为json的Postgres数组:json[]，追加新元素并将该Postgres数组转换回json值.
  array_to_json()足够聪明，不再需要对Postgres数组的json元素进行编码.

• either cast the json value to text, concatenate the new element, generating valid JSON syntax and transform back,
• or transform to a Postgres array of json: json[], append the new element and transform this Postgres array back to a json value.
  array_to_json() is smart enough to not encode the json elements of the Postgres array another time.

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

The first approach is much more error prone. You would have to build valid JSON by hand. Here is an implementation of the second variant:

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语句.

The function wrapper is mostly irrelevant to the problem. Could just be a plain SQL statement.

相关:

• 如何将json数组转换为postgres数组?

这篇关于使用aggregrate函数更新json数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！