PostgreSQL Nested JSON Field Indexing With "@" Operator

Question

I have a table in PostgreSQL that contain three 2 fields, that are name and info. The name column is the user (customer) name as string/text type and the info column is the user detail as jsonb type.

The jsonb data example from the info column as below:

{
 "id": "1",
 "carts": [
     {
       "product_id": "001",
       "price": 50
     },
     {
       "product_id": "",
       "price": 30
     }
 ]
}

Based on the data, I have a query to find specific user from the product id as below.

SELECT * FROM users WHERE info -> 'carts' @> ('[{"product_id": "' || '001' || '"}]')::jsonb

So, is there any suggestion for the best and suitable query for indexing my use case above?

Thank you

PostgreSQL Nested JSON Field Indexing With "@>" Operator

0 Answers