对PostgreSQL JSONB列的查询

Question

我有一个表格"blobs"与列元数据"在json数据类型中，例子:

{
"total_count": 2,
"items": [
{
"name": "somename",
"metadata": {
"metas": [
{
"id": "11258",
"score": 6.1,
"status": "active",
"published_at": "2019-04-20T00:29:00",
"nvd_modified_at": "2022-04-06T18:07:00"
},
{
"id": "9251",
"score": 5.1,
"status": "active",
"published_at": "2018-01-18T23:29:00",
"nvd_modified_at": "2021-01-08T12:15:00"
}
]
}
]
}

我想在"元"中识别状态。与特定给定字符串匹配的数组。到目前为止，我已经尝试了以下操作，但没有结果:

SELECT * FROM blobs 
WHERE metadata is not null AND
(
SELECT count(*) FROM jsonb_array_elements(metadata->'metas') AS cn
WHERE cn->>'status' IN ('active','reported')
) > 0;

如果我能将字符串与"status"进行比较就足够了。在第一个数组对象中

我正在使用PostgreSQL 9.6.24

Answer 1

为了清晰起见，我通常将代码分解为一系列WITH语句。我对你的问题的想法是使用json路径(https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATH)和函数jsonb_path_query。下面的代码给出了一个计数列表，我将把剩下的留给你，以获得最终数据。我添加了ID列只是为了有东西可以连接。否则连接元数据

还要注意额外的"在什么条件下。blob_ext中的左连接只是在元数据不存在或该路径不工作时具有空值。

with blob as (
select row_number() over()"id", * from (VALUES
(
'{
"total_count": 2,
"items": [
{
"name": "somename",
"metadata": {
"metas": [
{
"id": "11258",
"score": 6.1,
"status": "active",
"published_at": "2019-04-20T00:29:00",
"nvd_modified_at": "2022-04-06T18:07:00"
},
{
"id": "9251",
"score": 5.1,
"status": "active",
"published_at": "2018-01-18T23:29:00",
"nvd_modified_at": "2021-01-08T12:15:00"
}
]
}
}
]}'::jsonb), 
(null::jsonb)) b(metadata)
)
, blob_ext as (
select bb.*, blob_sts.status
from blob bb
left join (
select
bb2.id,
jsonb_path_query (bb2.metadata::jsonb, '$.items[*].metadata.metas[*].status'::jsonpath)::character varying "status"
FROM blob bb2
) as blob_sts ON
blob_sts.id = bb.id
)
select bbe.id, count(*) cnt, bbe.metadata
from blob_ext bbe
where bbe.status in ('"active"', '"reported"')
group by bbe.id, bbe.metadata;

Answer 2

一种方法是用jsonb_extract_path()和jsonb_array_elements()一次剥离一层:

with cte_items as (
select id,
metadata,
jsonb_extract_path(jx.value,'metadata','metas') as metas
from blobs, 
lateral jsonb_array_elements(jsonb_extract_path(metadata,'items')) as jx),
cte_metas as (
select id,
metadata,
jsonb_extract_path_text(s.value,'status') as status
from cte_items,
lateral jsonb_array_elements(metas) s)
select distinct 
id, 
metadata
from cte_metas
where status in ('active','reported');