json - Postgresql - 使用一个表中的选定列作为 json 选择语句
问题描述
https://www.db-fiddle.com/f/gZXz9hJRzpiEmDr7V8PXEG/0
PostgreSQL 10.x
考虑下表:
CREATE TABLE attributes (
attr TEXT
);
INSERT INTO attributes VALUES('sth1');
INSERT INTO attributes VALUES('sth2');
CREATE TABLE items (
name TEXT,
custom JSONB
);
INSERT INTO items VALUES ('A', '{"sth1": "Hello"}');
INSERT INTO items VALUES ('B', '{"sth1": "Hello", "sth2": "Okay"}');
INSERT INTO items VALUES ('C', '{"sthNOT": "Hello", "sth2": "Okay"}');
我的目标是仅将attributes
表中的列作为列中的 Json 键ìtems.custom
查询 - 因此查询始终返回相同的键集。
当我知道我会做的列时:
SELECT name, custom->>'sth1', custom->>'sth2' FROM items;
我想让这个查询“动态” - 所以attributes
表中可以有任意键定义。
我也可以在查询中创建一个新的 Json 对象 - 仅包含表中定义的键和列attributes
中的相应值items.custom
。因此,将一个从 中创建的 Json 对象attributes
与items.custom
数据合并是一种选择。
有没有办法在 Postgres 中实现这一点?
解决方案
您需要一个函数来动态格式化和执行合适的查询。该函数返回带有name
jsonb 对象的行data
:
create or replace function select_from_items()
returns table(name text, data jsonb) language plpgsql as $$
declare
cols text;
begin
select string_agg(format('%1$L, custom->>%1$L', attr), ', ')
from attributes
into cols;
return query execute format('
select name, jsonb_strip_nulls(jsonb_build_object(%s))
from items', cols);
end $$;
select * from select_from_items();
name | data
------+-----------------------------------
A | {"sth1": "Hello"}
B | {"sth1": "Hello", "sth2": "Okay"}
C | {"sth2": "Okay"}
(3 rows)
因此,这是您的第二选择。第一个假设创建一种数据透视表并且需要更复杂的技术,例如
推荐阅读
- android - 为什么我不能从 Android 向实时数据库发送数据?
- docker - 发布 FastAPI docker-container 时出现 422 Unprocessable Entity 错误,但在未 dockerized 时有效
- function - 有没有像 sqlite3“函数”这样的东西?
- r - 计算 R 中每一列的出现次数
- airflow - 如何解码气流表 dag_run 列 conf 值
- python - 如何更改python模块中的函数?
- c - 为什么我的线性代数 C 程序会因非方阵而失败?
- json - 如何检索本地 json 数据以将其显示在谷歌地图上?
- azure - 来自联合 Azure 租户的用户可以使用相同的电子邮件向我的 Azure 租户注册吗?
- azure-functions - Azure 函数消耗计划超时