Querying an array of objects in postgres-CodePudding

myTable
{
 "creator": "[email protected]",
 "data": {
  "sections" : [],
  "sharing": [
    {
     "key": "value",
      "user": "[email protected]"
    },
    {
     "key": "value",
     "user": "[email protected]"
    },
    {
     "key": "value",
     "user": "[email protected]"
    }
   }
}

I have above table stored in postgres, and data column stored in jsonb format, I want to query with user email eg. user : [email protected]

I have tried below query but the response is slow

SELECT creator, data - 'sections' AS data FROM myTable WHERE (data->'sharing')::text LIKE '%[email protected]%' ORDER BY xmin::text::bigint DESC

CodePudding user response：

Use the contains operator @>

select ..
from mytable
where data -> 'sharing' @> '[{"user": "[email protected]"}]'