skip to Main Content

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

2

Answers


  1. 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;
    
    Login or Signup to reply.
  2. 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');
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search