-- agg -> aggregation GROUP BY !! JSONB_AGG --将所有行数都集中在一个JSON的Array里，每一行对应一个Object，这个Object的每个key对应了表格的每个column（字段） SELECT id, jsonb_agg(express) FROM express; --> ERROR, 因为agg集合了所有行(类似 GROUP BY) SELECT id, jsonb_agg(express) FROM express GROUP BY id; --> OK SELECT uid, jsonb_agg(tmp) FROM buy AS tmp GROUP BY uid;--> OK SELECT jsonb_agg(id) FROM cs; --> [11, 7, 9, 26, 12, 14, 8, 10, 13] SELECT jsonb_agg(cs) FROM cs; --> [{"id": 11, "name": "Soul-Clinic", "online": ...}, {"id": 7, "name" ...} ...] jsonb_agg(table_or_field)::text => Will get an array of objct [{ id:..., name: ...}, { id: ..., name: ...}] TO_JSONB -- 每一行对应一行，对应一个 JSON 对象，若需要请求单行的数据，用这个更合适（例如某个产品的信息），里面的字段可以能是搭配 JSONB_AGG 使用 SELECT cover, uid, to_jsonb(tmp) FROM (select id, uid, title, cover FROM buy) AS tmp; SELECT to_jsonb(tmp) FROM (select id, uid, title, cover FROM buy) AS tmp ORDER BY id desc; --> 多行 SELECT jsonb_agg(distinct tmp ORDER BY id DESC) --> ORDER BY 在里面! FROM (select id, uid, title, cover FROM buy) AS tmp; --> 单行 -- https://www.postgresql.org/docs/current/intarray.html -- CREATE EXTENSION intarray; -- DROP EXTENSION intarray; ARRAY_AGG -- 与 JSONB_AGG 类似,但是返回的是数组,即只取值,不取字段名,而且下标是从 1 开始的,而不是 0 !!! array_agg(field) => Will get an array/list of field [1, 2, 3], the field maybe the table name, which will return row of all fields SELECT (array_agg(cs ORDER BY id DESC))[1] FROM cs; SELECT unnest(array_agg(cs)) FROM cs; SELECT unnest(array_agg(name)) FROM cs; -- SELECT unnest(array_agg(cs.name)) FROM cs; -- UNNEST => This function is used to expand an array to a set of rows. SELECT unnest(ARRAY[1,2]); SELECT array_agg(DISTINCT tmp ORDER BY tmp DESC) FROM (select id, uid, title, cover FROM buy) AS tmp; --> DISTINCT SELECT array_agg(DISTINCT title ORDER BY title DESC) FROM (select id, uid, title, cover FROM buy) AS tmp; STRING_AGG Concatenate a string field of all rows -- 搭配上 GROUP BY 就是有用 SELECT string_agg(name, ', ' ORDER BY id DESC) FROM cs; SELECT array_to_string(array_agg(cs.name ORDER BY id DESC), ', ') FROM cs; --> | Soul-Clinic, 会种小助手, User-9, User-26, I am me, 忧郁海岸, User-8, 心灵诊室, User-13 | SELECT online, string_agg(name, ', ') AS users FROM cs GROUP BY online; /*--------+-----------------------------------------+ | online | users | +--------+-----------------------------------------+ | f | User-26, 忧郁海岸 | | - | Soul-Clinic, I am me, 心灵诊室, User-13 | | t | 会种小助手, User-9, User-8 | +--------+-----------------------------------------*/ -- ARRAY_AGG => Make several rows to 1 row UPDATE product SET tids = (SELECT array_agg(id) FROM product_tags WHERE pid = product.id); 2020-07 jsonb_agg(tmp)::text => Will get an array of objct [{ id:..., name: ...}, { id: ..., name: ...}] to_jsonb(tmp)::text => Will get only single row! array_agg(field) => Will get an array/list of field [1, 2, 3] SELECT info->'avatarUrl' FROM client WHERE jsonb_extract_path_text(info, 'country') = 'China'; UPDATE admin SET name = 'Savior', info = jsonb_set(info, '{level}', '"Super Level!"'); UPDATE product SET content = jsonb_set(content, '{1, src}', replace((content->1->'src')::text, 'https://shop.com', 'http://shop.celwk.com')::jsonb) WHERE id = 11; select '{"name":"francs","age":"31"}'::jsonb || '{"age":"32"}'::jsonb; -- ?column? --------------------------------- {"age": "32", "name": "francs"} SELECT jsonb_pretty('{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb); jsonb_pretty --------------------------------- { + "name": "James", + "contact": { + "fax": "01987 543210", + "phone": "01234 567890"+ } + } 2020-07-18 SELECT id, status#>>'{result, list, 0, status}' as status FROM express; /* { "result": {"list": [{"time": "2020-06-22 13:29:58", "status": "快件已在 【惠州江北】 签收, 签收人: 保安室E架, 如有疑问请电联:（13202333962）, 投诉电话:（0752-2801772）, 您的快递已经妥投。风里来雨里去, 只为客官您满意。上有老下有小, 赏个好评好不好？【请在评价快递员处帮忙点亮五颗星星哦~】"}, {"time": "2020-06-22 06:51:11", "status": "【惠州江北】 的谭海波（13202333962） 正在第1次派件, 请保持电话畅通,并耐心等待（95720为中通快递员外呼专属号码，请放心接听）"}, {"time": "2020-06-22 06:50:05", "status": "快件已经到达 【惠州江北】"}, {"time": "2020-06-22 02:09:45", "status": "快件离开 【东莞中心】 已发往 【惠州江北】"}, {"time": "2020-06-22 00:55:45", "status": "快件已经到达 【东莞中心】"}, {"time": "2020-06-20 21:15:27", "status": "快件离开 【淮安中转】 已发往 【东莞中心】"}, {"time": "2020-06-20 21:12:41", "status": "快件已经到达 【淮安中转】"}, {"time": "2020-06-20 17:34:21", "status": "快件离开 【盐城】 已发往 【 淮安中转】"}, {"time": "2020-06-20 13:35:04", "status": "【盐城】（0515-88355806、0515-88339666） 的 金邦（15105108372） 已揽收"}] }}*/ select jsonb_agg(pts)#>>'{0, unit}' from pts; SELECT json_build_object('who', "creator_aid", 'time', time) FROM product; +----------------------------------------------------+ | json_build_object | +----------------------------------------------------+ | {"who" : 2, "time" : "2020-04-29T15:22:36.587549"} | | {"who" : 2, "time" : "2020-05-27T06:14:56.999856"} | | {"who" : 1, "time" : "2020-04-29T15:22:45.081184"} | +----------------------------------------------------+ SELECT json_agg( json_build_object('who', "creator_aid", 'time', time) ) FROM product; +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | json_agg | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | [{"who" : 2, "time" : "2020-04-29T15:22:36.587549"}, {"who" : 2, "time" : "2020-05-27T06:14:56.999856"}, {"who" : 1, "time" : "2020-04-29T15:22:45.081184"}] | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ SELECT json_agg( json_build_object('id', "id", 'title', title) ) FROM product; +-----------------------------------------------------------------------------------------------------------+ | json_agg | +-----------------------------------------------------------------------------------------------------------+ | [{"id" : 2, "title" : "贵族苹果"}, {"id" : 5, "title" : "贵族猕猴桃"}, {"id" : 11, "title" : "水果精品"}] | +-----------------------------------------------------------------------------------------------------------+ SELECT json_agg(admin) FROM admin; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | json_agg | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | [{"id":1,"name":"超管Can","phone":"15089323844","password":"qqqq","info":{"name": "Super Can", "level": "Super Level!"},"time":"2020-05-05T17:36:11.723167","level":"super","count_chat":21}, +| | {"id":2,"name":"黑客😎","phone":"15000000000","password":"qqqq","info":{"name": "Lisp", "level": "normal"},"time":"2020-05-09T19:32:00.025267","level":"normal","count_chat":1}] | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ (1 row) SELECT id, additions, (jsonb_array_elements(additions))->>'icon' as icon FROM product ; --Get multiple lines of a jsonb array (rows count = array length) +----+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+ | id | additions | icon | +----+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+ | 11 | [{"icon": "/ui/svg/megaphone.svg", "text": "Do it！", "color": "#f25209"}] | /ui/svg/megaphone.svg | | 2 | [{"icon": "/ui/svg/key.svg", "text": "最后五件", "color": "#5a7510"}, {"icon": "/ui/svg/medical.svg", "text": "最后3件", "color": "red"}] | /ui/svg/key.svg | | 2 | [{"icon": "/ui/svg/key.svg", "text": "最后五件", "color": "#5a7510"}, {"icon": "/ui/svg/medical.svg", "text": "最后3件", "color": "red"}] | /ui/svg/medical.svg | +----+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+