查询以将json对象数组插入postgres [英] Query to insert array of json object into postgres
本文介绍了查询以将json对象数组插入postgres的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表,其中的字段为 fields json []
。我创建了一个将记录插入到该表中的函数,这是调用
I have a table with field fields json[]
. I have created a function to insert the record into that table and here is the call
select * from add('[{"name":"Yes","value":"yes"},{"name":"No","value":"no"},{"name":"Neutral","value":"neutral"}]') as result;
这是函数本身
CREATE OR REPLACE FUNCTION public.add(
_fields json[]) RETURNS SETOF json
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
query text;
BEGIN
insert into my_table(fields)
values(_fields);
query = 'SELECT json_build_object(''message'', ''Added.'')';
RETURN QUERY EXECUTE query;
END;
$BODY$;
但这显示
malformed array literal: "[{"name":"Yes","value":"yes"},{"name":"No","value":"no"},{"name":"Neutra
然后我尝试了此
select * from add('{"name":"Yes","value":"yes"},{"name":"No","value":"no"},{"name":"Neutral","value":"neutral"}') as result;
,此插入语句的功能类似于
and this insert statement in function like
insert into polls(fields) values(array([''||_fields||'']::json[]));
这表明
syntax error at or near "["
或附近的语法错误
推荐答案
我每个对象都缺少
。这是创建json对象的方法
I was missing "
for each individual object. This is how to create json object
{"{\"name\":\"Yes\",\"value\":\"yes\"}","{\"name\":\"No\",\"value\":\"no\"}","{\"name\":\"Neutral\",\"value\":\"neutral\"}"}
所以最终查询是
select * from add('{"{\"name\":\"Yes\",\"value\":\"yes\"}","{\"name\":\"No\",\"value\":\"no\"}","{\"name\":\"Neutral\",\"value\":\"neutral\"}"}') as result;
这篇关于查询以将json对象数组插入postgres的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文