Postgres的从jsonb阵列
问题描述:
提取值我有一个jsonb场与像下面这样一个阵列:Postgres的从jsonb阵列
[
{
"type":"discount",
"title":"Discount 10%"
},
{
"file":"zx5rP8EoacyfhqGndcSOnP8VYtkr9Ya8Nvf7oYL98YDsM1CLMYIurYvfVUU4AGkzBsovwssT0bq.pdf",
"type":"menu",
"title":"Some menu title etc"
}
]
我想要得到以防文件属性有一个类型=菜单在阵列。
我设法做的是知道是否有一个,但我怎样才能最终提取文件的价值?
case when offers @> '[{"type":"menu"}]' then true else false end
我不想这样做下面的事情,因为数组可能不包含折扣类型。
offers->1->'file'
答
使用jsob_array_elements()
和->>
操作符(见JSON Functions and Operators。)
with a_table(json_col) as (
values (
'[
{
"type":"discount",
"title":"Discount 10%"
},
{
"file":"zx5rP8EoacyfhqGndcSOnP8VYtkr9Ya8Nvf7oYL98YDsM1CLMYIurYvfVUU4AGkzBsovwssT0bq.pdf",
"type":"menu",
"title":"Some menu title etc"
}
]'::jsonb)
)
select value->>'file' as filename
from a_table,
lateral jsonb_array_elements(json_col)
where value->>'type' = 'menu'
filename
---------------------------------------------------------------------------------
zx5rP8EoacyfhqGndcSOnP8VYtkr9Ya8Nvf7oYL98YDsM1CLMYIurYvfVUU4AGkzBsovwssT0bq.pdf
(1 row)
答
如:
t=# with a as (with v as (select '[
{
"type":"discount",
"title":"Discount 10%"
},
{
"file":"zx5rP8EoacyfhqGndcSOnP8VYtkr9Ya8Nvf7oYL98YDsM1CLMYIurYvfVUU4AGkzBsovwssT0bq.pdf",
"type":"menu",
"title":"Some menu title etc"
}
]'::jsonb j)
select jsonb_array_elements(j) r from v) select r->>'file' from a where r->>'type' = 'menu';
?column?
---------------------------------------------------------------------------------
zx5rP8EoacyfhqGndcSOnP8VYtkr9Ya8Nvf7oYL98YDsM1CLMYIurYvfVUU4AGkzBsovwssT0bq.pdf
(1 row)
尝试'json_array_elements'这个.. –
@VaoTsun没有运气 – mallix