Postgresql实现递归查询字典并转JSON
2022/3/18 19:27:33
本文主要是介绍Postgresql实现递归查询字典并转JSON,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
现在有一张字典表,有二级 、三级字典项,需要递归查询并转换成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": [] } ] }
这篇关于Postgresql实现递归查询字典并转JSON的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-21Vue3教程:新手入门到实践应用
- 2024-12-21VueRouter4教程:从入门到实践
- 2024-12-20Vue3项目实战:从入门到上手
- 2024-12-20Vue3项目实战:新手入门教程
- 2024-12-20VueRouter4项目实战:新手入门教程
- 2024-12-20如何实现JDBC和jsp的关系?-icode9专业技术文章分享
- 2024-12-20Vue项目中实现TagsView标签栏导航的简单教程
- 2024-12-20Vue3入门教程:从零开始搭建你的第一个Vue3项目
- 2024-12-20从零开始学习vueRouter4:基础教程
- 2024-12-20Vuex4课程:新手入门到上手实战全攻略