Querying an array of objects in postgres

Question

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

score 1 · Accepted Answer · 2021-12-15 10:17:56Z

1

Use the contains operator @>

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

edited Dec 15, 2021 at 10:17

answered Dec 15, 2021 at 6:34

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Ajay Yadav Over a year ago

Thanks, but this is not working its not returning anything SELECT creator, data FROM myTable where data->'sharing' @> '{"user" : "[email protected]"}'

user330315 Over a year ago

Ah, sorry. The parameter also needs to be an array. See my edit

Collectives™ on Stack Overflow

Querying an array of objects in postgres

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related