Fork me on GitHub

彭占元

吃葡萄不吐葡萄皮

0%

jsonb的遍历

发表于 分类于 阅读次数： Valine：

此文旨在记录jsonb的一些sql遍历操作

插入测试数据

1
2
3
4
5
6
7
8
9
10
create table test_jsonb(id serial primary key,msg jsonb);
insert into test_jsonb VALUES (1,'{"a":"foo", "b":"bar"}');
insert into test_jsonb values( 2,'{"RootModule":{"path":[1],"tags":{"ModuleBase1":{"value":40640,"humanstring":"40640"}}}}');

my_testdb=#  select * from test_jsonb;
 id |                                               msg                                                
----+--------------------------------------------------------------------------------------------------
  1 | {"a": "foo", "b": "bar"}
  2 | {"RootModule": {"path": [1], "tags": {"ModuleBase1": {"value": 40640, "humanstring": "40640"}}}}
(2 rows)

获取最外层的key/value

1
2
3
4
5
6
7
my_testdb=# select id, key, value from test_jsonb,jsonb_each(msg);
 id |    key     |                                      value                                       
----+------------+----------------------------------------------------------------------------------
  1 | a          | "foo"
  1 | b          | "bar"
  2 | RootModule | {"path": [1], "tags": {"ModuleBase1": {"value": 40640, "humanstring": "40640"}}}
(3 rows)

递归获取每一层的key/value

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
my_testdb=#  with recursive flat (id, path, value) as ( 
                                    select id, key, value from test_jsonb, jsonb_each(msg)
                                    union
                                    select f.id, concat(f.path, '.', j.key), j.value from flat f, jsonb_each(f.value) j where jsonb_typeof(f.value) = 'object'
                                    )
                            select id, path, value from flat;
                            
my_testdb-# select id, path, value from flat;
 id |                  path                   |                                      value                                       
----+-----------------------------------------+----------------------------------------------------------------------------------
  1 | a                                       | "foo"
  1 | b                                       | "bar"
  2 | RootModule                              | {"path": [1], "tags": {"ModuleBase1": {"value": 40640, "humanstring": "40640"}}}
  2 | RootModule.path                         | [1]
  2 | RootModule.tags                         | {"ModuleBase1": {"value": 40640, "humanstring": "40640"}}
  2 | RootModule.tags.ModuleBase1             | {"value": 40640, "humanstring": "40640"}
  2 | RootModule.tags.ModuleBase1.value       | 40640
  2 | RootModule.tags.ModuleBase1.humanstring | "40640"
(8 rows)

添加过滤条件

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
my_testdb=# with recursive flat (id, path, value) as (
    select id, key, value from test_jsonb, jsonb_each(msg)
    union
    select f.id, concat(f.path, '.', j.key), j.value from flat f, jsonb_each(f.value) j
    where jsonb_typeof(f.value) = 'object')
select id, path, value from flat where path ~ 'RootModule.path';

 id |      path       | value 
----+-----------------+-------
  2 | RootModule.path | [1]
(1 row)



my_testdb=# with recursive flat (id, path, value) as (
    select id, key, value from test_jsonb, jsonb_each(msg)
    union
    select f.id, concat(f.path, '.', j.key), j.value from flat f, jsonb_each(f.value) j
    where jsonb_typeof(f.value) = 'object'
    )
select id, path, value from flat where path ~ any(array['humanstring','RootModule.path']);

id |                  path                   |  value  
----+-----------------------------------------+---------
  2 | RootModule.path                         | [1]
  2 | RootModule.tags.ModuleBase1.humanstring | "40640"
(2 rows)


-------------本文结束感谢您的阅读-------------
听说，打赏我的人都找到了真爱

欢迎关注我的其它发布渠道