If you can do this one without unnesting the downloads array I do not know how.
Having done that: (WHERE dlarray->'publd' = '123)
I think it’s doable like this:
select * from contacts where data @> '{"downloads":[{"pubid":123}]}'::jsonb
…which would be aided by a gin index on ‘data’ using jsonb_path_ops:
CREATE INDEX idx_data_path_ops on contacts USING gin (data jsonb_path_ops);
https://www.postgresql.org/docs/10/static/datatype-json.html#JSON-INDEXING
Best,
Steve.
This email is confidential. If you are not the intended recipient, please
advise us immediately and delete this message.
The registered name of Cantab- part of GAM Systematic is Cantab Capital
Partners LLP.
See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information
on confidentiality, the risks of non-secure electronic communication, and
certain disclosures which we are required to make in accordance with applicable
legislation and regulations.
If you cannot access this link, please notify us by reply message and we will
send the contents to you.
