RE: Working with JSONB data having node lists

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.