jsonb应用 jsonobjectkeys()返回一个对象中所有的键 plaintext teledb select from jsonobjectkeys((select rowtojson(t1) from t1 where id 1)); jsonobjectkeys id name (2 rows) teledb select from jsonobjectkeys((select fjsonb from tjsonb where id1)::json); jsonobjectkeys col col1 col2 (3 rows jsonb索引使用 TeleDB为文档jsonb提供了GIN索引,GIN索引可以被用来有效地搜索在大量jsonb 文档(数据)中出现的键或者键值对。 创建jsonb 索引 plaintext teledb create index tjsonbfjsonbidx on tjsonb using gin(fjsonb); CREATE INDEX teledb d+ tjsonb Table "public.tjsonb" Column Type Collation Nullable Default Storage Stats target Description +++++++ id integer plain fjsonb jsonb extended Indexes: "tjsonbfjsonbidx" gin (fjsonb) Has ROWIDs: yes Distribute By: HASH(id) Location Nodes: ALL DATANODES 测试查询的性能 plaintext teledb select count(1) from tjsonb; count 10000000 (1 row) teledb analyze tjsonb; ANALYZE 没有索引开销 plaintext teledb select from tjsonb where fjsonb @> '{"col1":9999}'; id fjsonb + 9999 {"col1": 9999, "col2": "9999"} 9999 {"col1": 9999, "col2": "9999"} 9999 {"col1": 9999, "col2": "9999"} 9999 {"col1": 9999, "col2": "9999"} 9999 {"col1": 9999, "col2": "9999"} (5 rows) Time: 2473.488 ms (00:02.473) 有索引开销 plaintext teledb select from tjsonb where fjsonb @> '{"col1":9999}'; id fjsonb + 9999 {"col1": 9999, "col2": "9999"} 9999 {"col1": 9999, "col2": "9999"} 9999 {"col1": 9999, "col2": "9999"} 9999 {"col1": 9999, "col2": "9999"} 9999 {"col1": 9999, "col2": "9999"} (5 rows) Time: 217.968 ms