使用aggregate函数更新json数组

本文是小编为大家收集整理的关于使用aggregate函数更新json数组的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

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

推荐答案

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

由于Postgres无法直接操纵JSON数组(这只是有效的 json value to Postgres，而不是一个数组)您必须...

将json值铸成text，将新元素加成，生成有效的JSON语法然后转换，
或转换为json的Postgres数组:json[]，附加新元素并将此Postgres数组转换回json值.
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语句.

问题描述

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 looks something like this:

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

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.

My code is as follows:

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;

I get the following error:

Cannot use aggregate function in UPDATE

which I believe is complaining about array_agg().

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.

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

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.

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;

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

How to turn json array into postgres array?

查看全文

上一篇：Spark scala-parse json from dataframe column and return RDD with columns

下一篇：从MySQL结果和PHP为D3.js树创建分层JSON？