Filter rows by jsonb key mask

Question

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_

Answer 1

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%');