CODEWITHSUNDEEP
sqlpostgresqlsql-likepostgresql-9.2
Souvik Ray
Souvik Ray

Reputation: 3038

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?

Upvotes: 1

Views: 1023

Answers (2)

user330315
user330315

Reputation:

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You have a really bad data structure. Postgres supports a JSON type, so you could use that. However, even with a JSON type, your question would be a bit challenging.

More importantly, you seem to want this information in columns so you should put it there.

Given the constraints in your question, there is a simple solution if I assume that , does not appear inside the key/value pairs. If so:

select date, tags
from webhook_forecastmodel
where tags LIKE '%"table": "bank_trans"%' AND
      tags LIKE '%"metric": "balance"%' AND
      tags LIKE '%"account_id": "477"%' AND
      tags NOT LIKE '%,%,%,%';  -- not more than three key/value pairs

Upvotes: 1

Related Questions