0

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_

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.