PostgreSQL – Filter Rows by JSONB Key Mask

jsonpostgresql

1. I have table UserData with following schema:

ID – bigint
UserID – bigint
Data – jsonb

2. Data column jsonb has following structure:

date – contain timestamp
text – contain description
photo_* – contains photo's link (sometimes absent in json)

3. Key photo_*:**

In photo_* key of jsonb Data field there are could be different values instead of asteriks (*), e.g.: photo_640, photo_1024, photo_1600

4. What do I need

I need to filter out only those rows in table UserData, that have any photo_* in jsonb Data column, i.e. Data field should contain key that starts with photo_

Best Answer

I have a hard time understanding how exactly your JSON values looks like, but I think you want something like this:

select ud.*
from userdata ud
where exists (select *
              from jsonb_object_keys(ud.data) as t(ky)
              where t.ky like 'photo%');

Related Solutions

PostgreSQL – Filtering on Array Length Inside JSON

Without knowing your data, I can only guess that the selectivity of your index is low (which happen if the length of the array does not vary much).

One trick to overcome this might be changing the query slightly and creating a covering index. For this, choose a NOT NULL column (for example, the primary key of the table) to count, and then include this column in the index:

CREATE INDEX n_items ON your_table (jsonb_array_length(data->'items'), id);

SELECT count(id) 
  FROM your_table
 WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

This will hopefully turn into an index-only scan (I tested this omitting the jsonb part, but you will be able to tell if it works).

PostgreSQL 9.5 – Query Performance Impact of JOINed Column in SELECT Clause

After trying various configuration, I think I found the answer to my own question. It's not 100% proven but it makes sense.

The COUNT(*) OVER() as total_rows command executes the entire query, including the WHERE clause AND the SELECT fields. So when the result set is very big, much larger than the LIMIT 1000, we would still be copying all the matching records according to the columns indicated in SELECT, including the respayload.

Since respayload can be fairly big, when the unlimited response count is high, let's say 10K, then 10K respayloads are copied, only to later be discarded, as the copy of the selected columns happens again for only the first 1000 (because of limit) records.

That's why omitting respayload from the statement improved performance so drastically.

I will be careful in the future where I use COUNT(*) OVER() as this seems to be seriously affected by the selected columns.

Best Answer

Related Solutions

PostgreSQL – Filtering on Array Length Inside JSON

PostgreSQL 9.5 – Query Performance Impact of JOINed Column in SELECT Clause

Related Question