Alternate to Like in jsonb postgres

1
votes

I have a Postgres table with more than 25M records, there I have a jsonb column called info.

sample json format:

{
"username1":{"name":"somename","age":22,"gender":"male"},
"fathername":{"name":"somename","age":22,"gender":"male"}
}

I am going to find number of records that match the 'user%' key value.

Here is my query

select count(1) from tablename where info::text like '%user%';

this query is working and I am getting result for it, but it is taking long time to execute this query in 25M records.

Is there any way that I can optimize this query or any alternate method to achieve it? Please help.

Thanks in advance!

2
jsonpostgresqljsonb
The query always yields 0. – klin

2 Answers

0
votes

As @klin as pointed out, this query will only give you 0:

select count(1) from tablename where info::text like 'user%';

Why because this is just a simple pattern match and you don't have any strings in your info column that begins with 'user'. You might have better luck with '%user%' but that's going to be awfully slow (unless you have trigram extension enabled and an index created)

If you want to count exactly how many users have the username1 set it's a really simple query. 

select count(1) from tablename where info ? 'username1'

This works on 9.5. If you have a finite number of 'user%' keywords, you can try something like this:

select count(1) from tablename where info ?| array['username1','username2',..]

If you are uncertain about the nature of the keys then you can do

SELECT COUNT(1) FROM (
    SELECT * FROM JSONB_EACH(info) FROM tablename
) AS a WHERE a.key like '%user%'
0
votes

Well, it looks ugly, but I think this could work:

select count(1) from (
    -- unnest an array of keys
    select id, unnest(array_agg(d.k)) as k from (
        -- extract info keys
        select id, jsonb_object_keys(info) as k from tablename
    ) as d
    where k like '%user%'
    group by id
) as q
group by id;