查询以将json对象数组插入postgres

苏尼尔·加格(Sunil Garg)

我有一张带桌子的桌子fields json[]我创建了一个将记录插入到该表中的函数,这是调用

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;

这个插入语句在函数中像

insert into polls(fields) values(array([''||_fields||'']::json[]));

这表明

syntax error at or near "["
苏尼尔·加格(Sunil Garg)

我缺少"每个单独的对象。这是创建json对象的方法

{"{\"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;

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章