CODEWITHSUNDEEP
postgresqljsonb
nitin
nitin

Reputation: 88

Postgresql Search for a value in jsonb object

I have a jsonb field in a table having values of the form

    {
  "message": {
    "sender": {
      "from": "91**********"
    },
    "channel": "some kind of text",
    "content": {
      "text": "some kind of text",
      "type": "text"
    },
    "recipient": {
      "to": "91**********",
      "recipient_type": "some kind of text"
    },
    "preferences": {
      "webHookDNId": "some kind of text"
    }
  },
  "metaData": {
    "version": "some kind of text"
  }
}

Now i want to search for all such value which in "to" key of the object has a certain phone number. i am using following query for this but it is not working 

select * from table_name where (column1::jsonb ? '91**********') ;

Upvotes: 2

Views: 6032

Answers (2)

jjanes
jjanes

Reputation: 44373

? looks for a top-level key. The JSON you show only has two top-level keys, "message" and "metadata". So of course they don't match to '91**********'.

You probably want the containment operator @>:

 @> '{"message":{"recipient":{"to":"91**********"}}}'

This will be supported by the either type of JSONB GIN index on your column.

Upvotes: 4

user330315
user330315

Reputation:

You can use the -> and ->> operators to extract the value from a key:

select *
from the_table
where (the_column -> 'recipient' ->> 'to') = '91**********';

Or the #>> operator

select *
from the_table
where the_column #>> '{recipient,to}' = '91**********';

Upvotes: 1

Related Questions