Querying over PostgreSQL JSONB column-CodePudding

I have a table "blobs" with a column "metadata" in jsonb data-type, Example:

{
  "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"
          }
        ]
      }
  ]
}

I want to identify statuses in the "metas" array that match with certain, given strings. I have tried the following so far but without results:

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;

It would also be sufficient if I could compare the string with "status" in the first array object.

I am using PostgreSQL 9.6.24

CodePudding user response：

for some clarity I usually break code into series of WITH statements. My idea for your problem would be to use json path (https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATH) and function jsonb_path_query. Below code gives a list of counts, I will leave the rest to you, to get final data. I've added ID column just to have something to join on. Otherwise join on metadata.

Also, note additional " in where condition. Left join in blob_ext is there just to have null value if metadata is not present or that path does not work.

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;

CodePudding user response：

A way is to peel one layer at a time with jsonb_extract_path() and 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');