CODEWITHSUNDEEP
sqlpostgresql
user1354934
user1354934

Reputation: 8841

How to filter on a json column for a specific value?

I'm on postgres and have a table orders with a data column which is jsonb. Here's a condensed example of data in one of them - they have UUID keys and a value of { id, value }

  {
    '36462bd9-4ffa-4ee3-9a04-c2eb7575fe6c': {
      id: '',
      value: '2020-04-20T01:32:14.017Z',
    },
    '9baaed61-1275-4bbc-ae4f-2994ec9f7fda': { id: '4', value: 'Paper Towels' },
  }

How can I do operations such as to find any orders where data has some UUID (ie. 9baaed61-1275-4bbc-ae4f-2994ec9f7fda) and { id: '4' }?

Upvotes: 0

Views: 28

Answers (1)

user330315
user330315

Reputation:

You can use the contains operator @>

select *
from the_table
where data @> '{"9baaed61-1275-4bbc-ae4f-2994ec9f7fda": {"id": "4"}}';

This assumes that the invalid JSON id: '4' from your question is really stored as "id":"4". If the value is stored as a number: "id": 4 then you need to use that in the comparison value.

Upvotes: 1

Related Questions