Index by subkey in jsonb
A jsonb GIN index with the jsonb_path_ops operator class would be an option.

But to just index (multiple) application #&gt;&gt; '{*, tour_id, id}') values per jsonb field (pseudocode, the * is illegal), I would write a tiny, IMMUTABLE SQL function to extract and cast the UUID fields to an UUID array (uuid[]) - and create an expression GIN index on that. Should be considerably smaller and hence faster:

CREATE OR REPLACE FUNCTION f_extract_tour_ids(jsonb)
  RETURNS uuid[] AS
$func$
SELECT ARRAY(SELECT (jsonb_array_elements($1) #&gt;&gt; '{tour_id, id}')::uuid)
$func$ LANGUAGE sql IMMUTABLE;


What was missing in my original answer: there is no GIN operator class for uuid[], yet (up to Postgres 10). See this thread on pgsql-hackers.

Update: Starting with Postgres 11, the additional module btree_gin provides the missing operator class.

You have to create it yourself (once per database):

CREATE OPERATOR CLASS _uuid_ops DEFAULT 
   FOR TYPE _uuid USING gin AS 
   OPERATOR 1 &amp;&amp;(anyarray, anyarray), 
   OPERATOR 2 @&gt;(anyarray, anyarray), 
   OPERATOR 3 &lt;@(anyarray, anyarray), 
   OPERATOR 4 =(anyarray, anyarray), 
   FUNCTION 1 uuid_cmp(uuid, uuid), 
   FUNCTION 2 ginarrayextract(anyarray, internal, internal), 
   FUNCTION 3 ginqueryarrayextract(anyarray, internal, smallint, internal, internal, internal, internal), 
   FUNCTION 4 ginarrayconsistent(internal, smallint, anyarray, integer, internal, internal, internal, internal), 
   STORAGE uuid;


Note (quoting the manual):


  The user who defines an operator class becomes its owner. Presently,
  the creating user must be a superuser.


See:


PostgreSQL GIN index on array of uuid


Then create the index:

CREATE INDEX payments_application_tour_ids_gin_idx ON payments
USING gin (f_extract_tour_ids(application));


Match the expression and use an indexable array operator in your queries to be applicable, like:

SELECT *
FROM   payment
WHERE  f_extract_tour_ids(application) @&gt; '{d61802ff-3eec-4a72-97ca-832f51b96bf0}'::uuid[];


As you can see, you can search for any number of UUIDs at once with this query.



Or, if you can't (or won't) create the operator class, you can work with the text representation of the UUID values - which is considerably bigger and hence more expensive.

Adapt f_extract_tour_ids(jsonb) like this:

CREATE OR REPLACE FUNCTION f_extract_tour_ids(jsonb)
  RETURNS text[] AS
$func$
SELECT ARRAY(SELECT (jsonb_array_elements($1) #&gt;&gt; '{tour_id, id}'))
$func$ LANGUAGE sql IMMUTABLE;


And the query:

SELECT *
FROM   payment
WHERE  f_extract_tour_ids(application) @&gt; '{d61802ff-3eec-4a72-97ca-832f51b96bf0}'::text[];


Be sure to match the UUID format exactly.

Related:


Index for finding an element in a JSON array
Can PostgreSQL index array columns?
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars