How to query Json array in jsonb in Postgresql

Question

I have a jsonb column which have following rows

ROW1:

 [
        {
            "cpe23Uri": "cpe:2.3:a:sgi:irix:3.55:*:*:*:*:*:*:*",
            "active": true
        },
        {
            "cpe23Uri": "cpe:2.3:a:university_of_washington:imap:10.234:*:*:*:*:*:*:*",
            "active": true
        }
    ]

ROW 2:

[]

ROW 3:

[
    {
        "cpe23Uri": "cpe:2.3:o:sgi:irix:*:*:*:*:*:*:*:*",
        "active": true
    }
]

I want to find the rows which contain sgi:irix in the key cpe23Uri

Which query should i use for best performance?

GMB · Accepted Answer · 2020-02-03 14:47:14Z

1

You could use an exists condition with a correlated subquery that uses jsonb_array_element() to unnest and search the array:

select *
from mytable t
where exists (
    select 1
    from jsonb_array_elements(t.js) x
    where  x->>'cpe23Uri' like '%sgi:irix%'
);

Demo on DB Fiddle

answered Feb 3, 2020 at 14:47

GMB

224k25 gold badges102 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Micheal Toru Over a year ago

It works great! Is there any way to make it faster? because it need about 3 seconds to get the results. I have to run this query for a big analyse 15M times which can take 1 month so.. any idea?

Micheal Toru Over a year ago

select * from t WHERE t.products @>'[{"cpe23Uri":"cpe:2.3:a:sgi:irix:0.1.0:*:*:*:*:*:*:*"}]'::jsonb; This query takes only 1 second but without LIKE operator..

Collectives™ on Stack Overflow

How to query Json array in jsonb in Postgresql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related