现在有一张字典表,有二级 、三级字典项,需要递归查询并转换成JSON对象。字典数据情况如下:
查询语句如下:
WITH RECURSIVE c AS ( SELECT dictionaries_id,BIANMA,NAME,parent_id, 0 as lvl FROM sys_dictionaries WHERE dictionaries_id ='91f9e33300824f84a2a0b8780775fb2f' AND parent_id='0' UNION ALL SELECT d.dictionaries_id,d.BIANMA,d.NAME,d.parent_id, c.lvl + 1 FROM sys_dictionaries d JOIN c ON d.parent_id = c.dictionaries_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).dictionaries_id AND l.lvl < maxlvl AND NOT EXISTS (SELECT 1 FROM c lp WHERE lp.parent_id = l.dictionaries_id)) r)) children FROM (SELECT c, j FROM c JOIN j ON j.parent_id = c.dictionaries_id) v GROUP BY v.c ) SELECT row_to_json(j) json_tree FROM j WHERE lvl = 0;
查询结果类似这样:
{ "dictionaries_id": "91f9e33300824f84a2a0b8780775fb2f", "bianma": "wp_sjyt", "name": "合法图斑实际用途", "parent_id": "0", "lvl": 0, "children": [ { "dictionaries_id": "ca97634ca1ef4ae79d2397d398b75595", "bianma": "01", "name": "商服用地", "parent_id": "91f9e33300824f84a2a0b8780775fb2f", "lvl": 1, "children": [ { "dictionaries_id": "b1e11a35b81b49e4a5d4d879d529d2f5", "bianma": "01_07", "name": "其他商服用地", "parent_id": "ca97634ca1ef4ae79d2397d398b75595", "lvl": 2, "children": [] }, { "dictionaries_id": "87f5bf6d27e244a5b75c3c46a3ad1e11", "bianma": "01_06", "name": "娱乐用地", "parent_id": "ca97634ca1ef4ae79d2397d398b75595", "lvl": 2, "children": [] }, { "dictionaries_id": "0ffbf2c61fee4886881ed9fbf4300a1b", "bianma": "01_05", "name": "商务金融用地", "parent_id": "ca97634ca1ef4ae79d2397d398b75595", "lvl": 2, "children": [] }, { "dictionaries_id": "5f74e7d1710c49ef84360d6a7ef5ce2a", "bianma": "01_04", "name": "旅馆用地", "parent_id": "ca97634ca1ef4ae79d2397d398b75595", "lvl": 2, "children": [] }, { "dictionaries_id": "0ad1d97089434c6b86b7b256bf572792", "bianma": "01_03", "name": "餐饮用地", "parent_id": "ca97634ca1ef4ae79d2397d398b75595", "lvl": 2, "children": [] }, { "dictionaries_id": "e010e20ee128459aa23bceef95d7f6ae", "bianma": "01_02", "name": "批发市场用地", "parent_id": "ca97634ca1ef4ae79d2397d398b75595", "lvl": 2, "children": [] }, { "dictionaries_id": "783da69c120c43d4bf23b9ea51ab5033", "bianma": "01_01", "name": "零售商业用地", "parent_id": "ca97634ca1ef4ae79d2397d398b75595", "lvl": 2, "children": [] } ] }, { "dictionaries_id": "62fe418f9b084811b86f53f412fb87c1", "bianma": "02", "name": "工矿仓储用地", "parent_id": "91f9e33300824f84a2a0b8780775fb2f", "lvl": 1, "children": [] }, { "dictionaries_id": "ea91d0e0035249d29755efbf9e386752", "bianma": "03", "name": "住宅用地", "parent_id": "91f9e33300824f84a2a0b8780775fb2f", "lvl": 1, "children": [] }, { "dictionaries_id": "74c1c1571bd9419fa59aa8dd7820bec5", "bianma": "04", "name": "公共管理与公共服务用地", "parent_id": "91f9e33300824f84a2a0b8780775fb2f", "lvl": 1, "children": [] }, { "dictionaries_id": "9369c75048aa489697a3242fd854802b", "bianma": "05", "name": "特殊用地", "parent_id": "91f9e33300824f84a2a0b8780775fb2f", "lvl": 1, "children": [] }, { "dictionaries_id": "1e05348899754affa2663c31cf9815d3", "bianma": "06", "name": "交通运输用地", "parent_id": "91f9e33300824f84a2a0b8780775fb2f", "lvl": 1, "children": [] }, { "dictionaries_id": "752406e52b9e473bb9f858268247a72e", "bianma": "07", "name": "水域及水利设施用地", "parent_id": "91f9e33300824f84a2a0b8780775fb2f", "lvl": 1, "children": [] }, { "dictionaries_id": "6ef459de851d48e1aae376150a74747e", "bianma": "08", "name": "其他土地", "parent_id": "91f9e33300824f84a2a0b8780775fb2f", "lvl": 1, "children": [] } ] }