Home > 语言编程 > [Python]PostgreSQL字典/JSON类型递归自展开

[Python]PostgreSQL字典/JSON类型递归自展开

PostgreSql 习惯上会将特殊数据类型的各个节点按字典/JSON类型存储
程序中需要获得完整的数据信息的时候,需要对这个节点进行自展开。

以下使用global id方式进行展开,一般适用于SQL+NoSQL结合的系统使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import sys, os
import numpy as np
 
def get_object_by_gid(id):
	for dict in data["json"]:
		if dict["gid"] == id:
	return dict.copy()
 
def self_exact_node(key):
	dict = get_object_by_gid(key)
	for k,v in dict.items():
		if k == "sub_item" :
			item_arr = []
			for id in v["gids"]:
				item_arr.append(self_exact_node(id))
			v["item_arr"] = item_arr.copy()
	return dict
 
def demo():
	data_exact = data.copy()
	for d in data_exact["json"]:
		d = self_exact_node(d["gid"])
 
def main():
	demo()
 
if __name__ == '__main__’:
	sys.exit(main())

其他玩法

1
2
3
4
5
6
7
8
# Global ID方式
"gid": "大分类2:中分类2:子分类2",
# 复合ID
"id":{"l1_cat":"大分类1","l2_cat":"中分类2","l3_cat":"子分类2"}
# 链表式
"chain":{"next_gid":"","pre_gid":"","head_gid":"","tail_gid":""}
# 二叉树式
"btree":{"next_sibling":"","child":”"}

测试用数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
data = {
    "json": [{
            "gid": "大分类1",
            "sub_item": {
                "gids": ["大分类1:中分类1", "大分类1:中分类2"]
            },
            "values": {
                "k1": "1000"
            }
        },
        {
            "gid": "大分类2",
            "sub_item": {
                "gids": ["大分类2:中分类1", "大分类2:中分类2"]
            },
            "values": {
                "k1": "2000"
            }
        },
 
        {
            "gid": "大分类1:中分类1",
            "sub_item": {
                "gids": ["大分类1:中分类1:子分类1", "大分类1:中分类1:子分类2"]
            },
            "values": {
                "k1": "1100"
            }
        },
        {
            "gid": "大分类1:中分类2",
            "sub_item": {
                "gids": ["大分类1:中分类1:子分类1", "大分类1:中分类1:子分类2"]
            },
            "values": {
                "k1": "1200"
            }
        },
 
        {
            "gid": "大分类2:中分类1",
            "sub_item": {
                "gids": ["大分类2:中分类1:子分类1", "大分类2:中分类1:子分类2"]
            },
            "values": {
                "k1": "2100"
            }
        },
        {
            "gid": "大分类2:中分类2",
            "sub_item": {
                "gids": ["大分类2:中分类2:子分类1", "大分类2:中分类2:子分类2"]
            },
            "values": {
                "k1": "2200"
            }
        },
 
        {
            "gid": "大分类1:中分类1:子分类1",
            "sub_item": {
                "gids": []
            },
            "values": {
                "k1": "1110"
            }
        },
        {
            "gid": "大分类1:中分类1:子分类2",
            "sub_item": {
                "gids": []
            },
            "values": {
                "k1": "1120"
            }
        },
        {
            "gid": "大分类1:中分类2:子分类1",
            "sub_item": {
                "gids": []
            },
            "values": {
                "k1": "1210"
            }
        },
        {
            "gid": "大分类1:中分类2:子分类2",
            "sub_item": {
                "gids": []
            },
            "values": {
                "k1": "1220"
            }
        },
 
        {
            "gid": "大分类2:中分类1:子分类1",
            "sub_item": {
                "gids": []
            },
            "values": {
                "k1": "2110"
            }
        },
        {
            "gid": "大分类2:中分类1:子分类2",
            "sub_item": {
                "gids": []
            },
            "values": {
                "k1": "2120"
            }
        },
        {
            "gid": "大分类2:中分类2:子分类1",
            "sub_item": {
                "gids": []
            },
            "values": {
                "k1": "2210"
            }
        },
        {
            "gid": "大分类2:中分类2:子分类2",
            "id":{"l1_cat":"大分类1","l2_cat":"","l3_cat":""}
            "sub_item": {
                "gids": []
            },
            "values": {
                "k1": "2220"
            }
        },
 
{
            "gid": "大分类2:中分类2:子分类2",
            /*复合ID*/
            "id":{"l1_cat":"大分类1","l2_cat":"中分类2","l3_cat":"子分类2"}
            /*链表式*/
            "chain":{"next_gid":"","pre_gid":"","head_gid":"","tail_gid":""}
            /*二叉树式*/
            "btree":{"next_sibling":"","child":""}
            "sub_item": {
                "gids": []
            },
            "values": {
                "k1": "2220"
            }
        }
    ]
}
Categories: 语言编程 Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.