代码之家  ›  专栏  ›  技术社区  ›  Sunil Garg

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

  •  2
  • Sunil Garg  · 技术社区  · 6 年前

    我有一张带字段的桌子 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语句的函数如下

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

    这是在展示

    syntax error at or near "["
    
    1 回复  |  直到 6 年前
        1
  •  2
  •   Sunil Garg    6 年前

    我失踪了 " 对于每个单独的对象。以下是如何创建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;