Search subkey inside JSONB

Question

In a PostgreSQL 9.6 database, I'm using this sql to get 'application' (field) -> 'tour_id.id' (key and subkey in jsonb field)

SELECT * 
FROM "payment" WHERE ("payment"."application"#>>'{}')::text 
LIKE '%a43d38d4-9bfb-4ffa-8704-e536f04b0c60%'
ORDER BY "payment"."createdAt" DESC;

My id is unique, but really I'd like to do it the right way in Postgres. (I know how to do it with a key, but not with a subkey.)

Example jsonb value:

[{"tour_id":
   {"id":"a43d38d4-9bfb-4ffa-8704-e536f04b0c60","name":"Terre Sainte 2018"},
   "amount":"500.00",
   "customer_id":{"id":"f492b26d-d260-4bcb-8e96-6532fdf38665","name":"OBAMA John"},
 "id":"5cd8338c-15c2-48c5-b305-9e172bb33a9a"}]

Answer 1

I'm using this sql to get 'application' (field) -> 'tour_id.id'

Assuming you meant to ask:

I'm using this SQL query to retrieve the row where application #>> '{0, tour_id, id}' equals the provided UUID.

.. which already carries half an answer.

If your outer JSON array only has a single element like your example value implies, use this simple query:

SELECT *
FROM   payment
WHERE  application #>> '{0, tour_id, id}' = 'a43d38d4-9bfb-4ffa-8704-e536f04b0c60'
ORDER  BY "createdAt" DESC;

Note how I replaced the (assumingly) unneeded fuzziness of
like '%a43d38d4-9bfb-4ffa-8704-e536f04b0c60%'
with simpler and faster
= 'a43d38d4-9bfb-4ffa-8704-e536f04b0c60'.

Else (for any number of elements in the outer JSON array):

SELECT *
FROM   payment
WHERE  application @> '[{"tour_id": {"id":"a43d38d4-9bfb-4ffa-8704-e536f04b0c60"}}]'
ORDER  BY "createdAt" DESC;

That's the jsonb containment operator @>, which can be supported with a generic index or a more specialized jsonb_path_ops index. Instructions and links:

• How to get particular object from jsonb array in PostgreSQL?

The manual hints:

For a full description of jsonb containment and existence semantics, see Section 8.14.3.