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"}]