How to search for exact pattern in a column using SQL LIKE operator?

I have a table which is like below

    date    |                                                   tags                                                   
------------+----------------------------------------------------------------------------------------------------------
 2018-10-24 | {"table": "bank_trans", "metric": "withdrawal", "location": "UK"}
 2018-10-24 | {"table": "bank_trans", "metric": "balance", "account_id": "477", "location": "ny", "country": "USA"}
 2018-10-24 | {"table": "bank_trans", "metric": "deposit", "location": "blr", "country": "IND"}
 2018-11-02 | {"table": "bank_trans", "metric": "balance", "account_id": "477"}

If I want a particular row that contains a search pattern something like below

select date, tags
from webhook_forecastmodel
where tags LIKE '%"table": "bank_trans"%' AND
      tags LIKE '%"metric": "balance"%' AND
      tags LIKE '%"account_id": "477"%';

In this case, I get back two results

    date    |                                                   tags                                                   
------------+----------------------------------------------------------------------------------------------------------

 2018-10-24 | {"table": "bank_trans", "metric": "balance", "account_id": "477", "location": "ny", "country": "USA"}

 2018-11-02 | {"table": "bank_trans", "metric": "balance", "account_id": "477"}

I understand the SQL query returns me the rows where the pattern matches.

But I only want the row that is exactly mentioned in the LIKE search pattern which is "table": "bank_trans", "metric": "balance" and "account_id": "477" which leaves us with only one row

 2018-11-02 | {"table": "bank_trans", "metric": "balance", "account_id": "477"}

Is there any possible way that this could be achieved?

Solution

UPDATE: this question assumes an up-to-date Postgres version. It will not work on the outdated and no longer maintained version 9.2, but I'm leaving it here anyway for reference.

As mentioned in the comments don't use LIKE, use JSON functions. In order to be able to do that, you have to cast the value:

select date, tags
from webhook_forecastmodel
where tags::jsonb @> '{"table": "bank_trans"}'::jsonb 
  AND tags::jsonb @> '{"metric": "balance"}'::jsonb 
  AND tags::jsonb @> '{"account_id": "477"'}::jsonb;

The @> operator checks if the value on the left side contains the key/value pair on the right side.

The above would also return rows that contain more than those key/value pairs. If you want those that contain exactly those key/value pairs, use =

select date, tags
from webhook_forecastmodel
where tags::jsonb = '{"table": "bank_trans", 
                      "metric": "balance", 
                      "account_id": "477"}'::jsonb;

The jsonb data type normalizes the key/value pairs, so the order of the keys is irrelevant and the = comparison will work correctly.

Online example: https://rextester.com/LYXHUC20162

In the online example I have a different order of keys in the tags column compared to those used for the = operator to demonstrate that JSONB normalizes the JSON representation.

Given the nature of your data, it's probably better to define the column as jsonb to avoid all the casting.