PostgreSQL中的递归JSON生成
我在PostgreSQL 9.5服务器中有以下表格:
值得注意的结构是,理论上位置是无限递归的.我需要从根位置生成JSON消息,递归到所有子位置;每个位置都有一些属性,一系列库存项目和一系列子位置. 如何为此创建高性能查询?我正在研究各种PostgreSQL JSON函数,LATERAL关键字,CTE,并且有点困惑.我已经使用非递归查询完成了JSON输出,但不确定如何干净地处理递归. 这是一个示例输出: { "id": 1000,"name": "By Location","type": "SITE","locations": [ { "id": 1005,"name": "Storage A","type": "STOR","locations": [ ...(same schema as parent)... ],"inventories": [ ...(see below for schema)... ] },{ "id": 1017,"name": "Storage B","name": "COLD","inventories": [...(see below for schema)... ] } ],"inventories": [ { "id": 5340,"product_id": 9120,"name": "Product X","thumb": "https://example.com/api/images/nnnn.jpg","sort_order": 1,"par_level": 3.5,"created": 1452898800,"updated": 1453071600,"measures": [ {"id": 3498,"quantity": 2.25,"created": 1453071600,"updated": 1453071600},{"id": 3456,"quantity": 3.25,"updated": 1452898800} ] } ] } 解决方法
让我们分成几块.首先,您将使用嵌套子查询来创建嵌套数组.公用表表达式可能有所帮助.
其他技巧是row_to_json和json_agg. 第一个问题是row_to_json需要将表作为参数来返回正确的标签. select json_agg(locations) from locations 将为每一行返回一个json对象.要仅使用某些字段,您需要创建一个类型并强制转换它或使用CTE和上面的语法.在大多数情况下,我会使用CTE. 所以你最终会得到类似的东西: WITH lowlevel1 AS ( SELECT a,b,c FROM tab1),lowlevel2 AS ( SELECT b,c,d FROM tab2),midlevel1 AS ( SELECT e,f,g,json_agg(lowlevel1) AS lab1,json_agg(lowlevel2) AS lab2 FROM tab3 LEFT OUTER JOIN lowlevel1 ON tab3.id = lowlevel1.parent LEFT OUTER JOIN lowlevel2 ON tab3.id = lovlevel2.parent) SELECT row_to_json(midlevel1) from midlevel1 或者在最后一行使用json_agg(midlevel1)而不是row_to_json(midlevel1)来返回所有行的一个数组. CTE还支持使用RECURSIVE修饰符进行回归.但是,它返回一个包含回归结果的表,而不是嵌套的JSON结构.因此,您可能需要显式编码所需的嵌套级别. 如果元素不存在,Postgres将返回null.例如,没有的子位置列表将返回“位置”:[null].为了用更有意义的结果替换它,<>然后<>否则'[]’结束或者如果<>然后<>否则'[]’结束可以使用.第一个是“搜索案例”,其中每个测试都是布尔表达式. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |