Postgres Pro
Downloads
Home   >   mailing lists

RE: Working with JSONB data having node lists - Mailing list pgsql-general

From Steven Winfield
Subject RE: Working with JSONB data having node lists
Date
Msg-id E9FA92C2921F31408041863B74EE4C2001A47A07DB@CCPMAILDAG03.cantab.local
Whole thread Raw
In response to Re: Working with JSONB data having node lists  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
Tree view

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.

pgsql-general by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: PostgreSQL Kerberos Authentication
Next
From: Pavel Suderevsky
Date:
Subject: Understanding Huge Pages