代码之家 › 专栏 › 技术社区 › JAGADEESH

如何在PostgreSQL中使用特定键的动态SQL查询提取嵌套的JSONB值?[副本]

jsonb query-optimization postgresql sql

JAGADEESH · 技术社区 · 10 月前

我有一个名为JSONB的列 metadata 在我的PostgreSQL数据库中。我想提取此列中特定键的值。

例如,考虑以下JSON结构:

{
  "key1": "value1",
  "key2": [
    {"key3": "value3"},
    {"key3": "value4"}
  ]
}

我需要检索以下值 key3 ,即使它们嵌套在不同的层次(高达4或5层深)。是否有一种通用的方法来为此目的动态生成PostgreSQL SQL查询?

1 回复 | 直到 10 月前

Zegarek 10 月前

这个 jsonb_path_query() 函数可以这样做: _{demo at db<>fiddle}

create table your_table(
   id int generated by default as identity primary key
  ,metadata  jsonb);
insert into your_table(metadata) values
 ('{"key1": "value1",
    "key2": [ {"key3": "value3"}
             ,{"key3": "value4"} ]
   }')
,('{"key3": "value5",
    "key2": [ {"key4": {"key3": "value6"}} ]
   }');

SELECT jsonb_path_query(metadata,'strict $.**.key3')
FROM your_table
WHERE metadata @? '$.**.key3';

jsonb_path_query
“值3”
“值4”
“值5”
“价值6”

$ 是jsonb结构的根
.** 意味着考虑了结构的所有层次
.key3 返回在任何级别上找到的该键下的值
strict mode 防止重复 value3 和 value4 由于访问而将返回 key2 否则默认情况下数组两次 lax 模式
WHERE metadata @? '$.**.key3' 条件使用a index 仅搜索具有以下条件的行 key3 在他们的任何地方

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

3 月前

Kallydi · jdbc无法连接到docker中的postgresql数据库

4 月前

dietzi96 · Pandas DataFrame.to_sql随机和静默地失败,没有错误消息

4 月前

Peter Schofield · 类型转换Postgresql

4 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

4 月前

Kevin Smeeks · Pyspark JDBC分区读取

4 月前

Micah Armantrout · 如何使用sql脚本将数据插入到代码优先的数据库中?

4 月前

Vanortton · 使用Docker开始应用程序开发还是在开发过程中配置Docker?[关闭]

4 月前

Andrus · 如何在sql中查找第二个匹配项

4 月前

MWood · Postgres jsonpath用于引用单例元素或元素数组

4 月前