前台: dhtmlxgrid.显示数据

　　  其格式为:

       

{　　rows:[　　　　 {id:1,data:[1,2,3]} ,{} ]}

如果在postgesql里直接生成这样的串呢??

这是就今天要做的事.

也是测试了一天,还是别人的帮助下完成:

1,2  本人自己写的,怎么也达不成这个目标: 

3,    群友给的方案:完美解决

 

--方案1: select json_agg(row_to_json(t))::text from (select id,concat_ws(',',pt_name,pt_description) as mydata from project_template )as t; --结果[{"id":1,"myData":"whq,admin"},{"id":2,"myData":"eathon,sys"}] --不理想 --方案2: select json_agg(row_to_json(t))::text from (select id,'['|| concat_ws(',',pt_name,pt_description) ||']' as mydata from project_template ) as t --结果[{"id":1,"myData":["whq,admin"]},{"id":2,"myData":["eathon,sys"]}] 好像对了,可是仔细看:"myData":["eathon,sys"] 　　　　不对,应该是:"myData":["eathon","sys"] --方案3: select json_agg(row_to_json(t))::text from (select id,array[pt_name,pt_description] as mydata from project_template where 1=1 and id=1) as t --结果:[{"id":1,"myData":["whq","admin"]},{"id":2,"myData":["eathon","sys"]}]--但是,大家注意了哟: 里面的数值是不带引号的,如果 用 JQUERY 的 $.getJSON()接受的话.里面的JSON 串 KEY,VALUE 都必须是双引号,否则不接受!--见另一篇文章

$.getJSON(url,function success(){})回调函数不起作用

  --那么问题来了:   --问题1:如果想返回所有的 KEY VALUE都带双引号该怎么办? -- select json_agg(row_to_json(t))::text from (select id::text,array[pt_name,pt_description] as mydata from project_template where 1=1 and id=1) as t --问题2: 如果查询的字段是个'col1,col2,col3'这样的变量应该怎么办? -- select array_to_string(string_to_array('col1,col2,col3',','),'::text,') --结果: col1::text,col2::text,col3

--快了,快要接近了:最后还少一个  ::text,因为要把所有的字段都给字符串化

-- select array_to_string(string_to_array('col1,col2,col3',','),'::text,')||'::text' --结果:col1::text,col2::text,col3::text

--这样才算完美解决!

--这里再次感谢  pumpkin 群友!(没有你的指点,我不知道要走多少弯路!)

--也放一个自己'研究'了N久的一个查询存储过程

--其可以完美解决 生成 标准的JSON(都带双引号)

 

create or replace function f_query_all_json( table_name text, --表名 query_feilds text, --需要查询的字段和字段值 'col1,col2,col3.......' condition_feilds text, --条件字段和字段值  "[{\"feild_name\":\"id\",\"feild_value\":1}]" 当然,这里应该可以简化一下 out return_value text --返回值 ) as $$ declare ex_sql text; recs record; _key text ; _value text; _result text; _counts integer; begin --这里有个问题.就是query_feilds 里面字段如果不是TEXT 类型,得加上强制转换::text select array_to_string(string_to_array($2,','),'::text,')||'::text' into _key; ex_sql:='select json_agg(row_to_json(t))::text from (select id::text,array['||_key||'] as data from ' ||quote_ident(table_name) || ' where 1=1 '; --设置条件参数 for recs in select * from json_array_elements(condition_feilds::json) loop _key := recs.value ->> 'feild_name'; _value := recs.value ->> 'feild_value' ; if json_typeof(recs.value -> 'feild_value') ='number' then ex_sql:=ex_sql|| ' and ' || _key || '=' || _value; else ex_sql:=ex_sql|| ' and ' || _key || ' like '''|| (recs.value ->> 'feild_value') || '%'''; end if; end loop; ex_sql:=ex_sql||') as t'; execute ex_sql into _value; _result:='{"rows":'|| _value||'}'; return_value := _result; -- return_value :=ex_sql; end; $$ language plpgsql;

 

 

 

   

 一个 很牛老外写的SQL语句

CREATE TABLE customer_area_node ( id bigserial NOT NULL PRIMARY KEY, customer_id integer NOT NULL, parent_id bigint, name text, description text ); INSERT INTO customer_area_node(customer_id, parent_id, name, description) VALUES (1, NULL, 'name1', '1'), (2, 1, 'name2', '1.2'), (3, 1, 'name3', '1.3'), (4, 2, 'name4', '1.2.4'), (5, 2, 'name5', '1.2.5'), (6, 3, 'name6', '1.3.6'), (7, 3, 'name7', '1.3.7'), (8, 5, 'name8', '1.2.5.8'), (9, 6, 'name9', '1.3.6.9'), (10, 3, 'name10', '1.3.10'), (11, 9, 'name11', '1.3.6.9.11'), (12, 11, 'name12', '1.3.6.9.11.12'); WITH RECURSIVE c AS ( SELECT *, 0 as lvl FROM customer_area_node WHERE customer_id = 1 AND parent_id IS NULL UNION ALL SELECT customer_area_node.*, c.lvl + 1 FROM customer_area_node JOIN c ON customer_area_node.parent_id = c.id ), maxlvl AS ( SELECT max(lvl) maxlvl FROM c ), j AS ( SELECT c.*, json '[]' children FROM c, maxlvl WHERE lvl = maxlvl UNION ALL SELECT (c).*, array_to_json(array_agg(j) || array(SELECT r FROM (SELECT l.*, json '[]' children FROM c l, maxlvl WHERE l.parent_id = (c).id AND l.lvl < maxlvl AND NOT EXISTS (SELECT 1 FROM c lp WHERE lp.parent_id = l.id)) r)) children FROM (SELECT c, j FROM c JOIN j ON j.parent_id = c.id) v GROUP BY v.c ) SELECT row_to_json(j) json_tree FROM j WHERE lvl = 0;

 

转载于:https://www.cnblogs.com/ssqhan/p/7424203.html