2

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

1 Answer 1

0

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:

The manual hints:

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

8
  • thanks, tour_id can be repeated inside the array, in elements 0,1,2 with different values so the right is application @> '[{"tour_id": {"id":"a43d38d4-9bfb-4ffa-8704-e536f04b0c60"}}] ? in that case how to index a subkey, inside in array is possible?
    – stackdave
    Commented Dec 18, 2017 at 3:35
  • it's very nice your solution about look for the first element, sometimes i need to look for that: application #>> '{0, tour_id, id}' , it would be nice if posgres can allo me look for application #>> '{*, tour_id, id}' , because @> maybe is slower, because look in subkeys of subkeys tree?
    – stackdave
    Commented Dec 18, 2017 at 3:38
  • @stackdave: Supported with a json_path_ops index, the 2nd query should be fast. To index only (multiple) id values, you need a special expression index, which might be considerably smaller ... If you need help with this index, ask a new question with exact details: is id a valid uuid? Typical queries - just what we see here? Commented Dec 18, 2017 at 4:41
  • thanks, i've just created a new question, trying give more details about the database logic
    – stackdave
    Commented Dec 18, 2017 at 5:03
  • hello again , how can i use @> to look with like '%Par%' [{"city":"Paris","country":"FRA"},{"city":"Parana","country":"ARG"}] ; write I another question ? thanks again
    – stackdave
    Commented Dec 18, 2017 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.