Postgres将JSONB单元扁平化为视图中的行 [英] Postgres flatten JSONB cell into row in view
问题描述
我有一个简单的postgres表:
I have a simple postgres table:
Column │ Type │ Modifiers
──────────────┼──────────────────────┼──────────────────────
id │ integer │ not null default
data │ jsonb │
这是data
的简化数据结构:
{
"id": 3,
"date": "2019-01-01",
"well_report_table":
[
{"element": "methane",
"yield": 6,
"price": 2.10
},
{"element": "pentane",
"yield": 6,
"price": 2.10
},
{"element": "butane",
"yield": 6,
"price": 3.50
}
],
"cost_report_table":
[
{"item": "fuel",
"charge": 6.30
},
{"item": "lease",
"charge": 200
}
]
}
我想在带有以下各列的视图中将其展平:
I'd like to flatten this in a view with the following columns:
id | date | well_report_table_methane_yield | well_report_table_methane_price | well_report_table_pentane_yield | well_report_table_pentane_price | well_report_table_butane_yield | well_report_table_butane_price |cost_report_table_fuel_charge | cost_report_table_lease_charge
我数组中的对象具有一个标识符,我想将其附加到数组对象名称中,然后遍历该对象中的其他键,并在 中创建列.
The objects in my arrays have an identifier that I would like to append to the array object name and then iterate through the other keys in the object and make columns out of .
这个问题使我接近: Postgres:展平聚合来自JSONB字段的键/值对?
This question gets me close: Postgres: Flatten aggregated key/value pairs from a JSONB field?
我不确定是否可以在plpgsql之类的工具中实现此功能,因此,如果我只需要使用脚本语言(如ruby/python)生成视图文本,然后再基于该脚本创建视图,则可以那个.
I'm not entirely sure this is possible in something like plpgsql, so if I just need to generate the view text in a scripting language like ruby/python and then create a view off of that, I'm a ok with that.
理想情况下,我可以使用jsonb_array_elements
和jsonb_each
之类的东西来避免中间表(我目前的所有尝试都需要中间视图),但是我还没有发现这种魔术组合. /p>
Ideally I'll be able to use something like jsonb_array_elements
and jsonb_each
in order to avoid intermediate tables (all of my current attempts have required intermediate views), but I haven't found that magic combination yet.
推荐答案
这不是关于展平JSON数组的普遍问题,因为在数组中隐藏了特定的逻辑.您可以在此函数中实现逻辑:
This is not a general question about flattening JSON arrays, because there is a specific logic hidden in the arrays. You can implement the logic in this function:
create or replace function flat_array(data jsonb, title text, item text)
returns jsonb language sql immutable as $$
select jsonb_object_agg(format('%s_%s_%s', title, elem->>item, key), value)
from jsonb_array_elements(data->title) as arr(elem)
cross join jsonb_each(elem)
where key <> item
$$;
查询:
select
jsonb_build_object('id', data->'id', 'date', data->'date') ||
flat_array(data, 'well_report_table', 'element') ||
flat_array(data, 'cost_report_table', 'item')
from my_table
提供对象:
{
"id": 3,
"date": "2019-01-01",
"cost_report_table_fuel_charge": 6.30,
"cost_report_table_lease_charge": 200,
"well_report_table_butane_price": 3.50,
"well_report_table_butane_yield": 6,
"well_report_table_methane_price": 2.10,
"well_report_table_methane_yield": 6,
"well_report_table_pentane_price": 2.10,
"well_report_table_pentane_yield": 6
}
可以通过从JSONB字段中平铺的聚合键/值对转换为表格视图吗?
这篇关于Postgres将JSONB单元扁平化为视图中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!