CODEWITHSUNDEEP
postgresql
Rohit
Rohit

Reputation: 13

Using select to find data in string with special characters

I am trying to filter out records where one specific field has special characters with words in them.

For example:

-[ RECORD 1 ]--------------+------------------------------ 
id                         | 3151 
description                | Manual add from SCCM 
mac_address                | d4258be8d064 
status                     | Unknown 
mac_vendor                 | Intel Corporate 
added_by                   | Policy Manager 
added_at                   | 2019-02-19 14:29:21.802413+00 
updated_at                 | 2022-10-19 10:57:15.960282+00
attributes                 | {"Our Device": "true"}
extras                     |  
org_id                     | 1 
permit_id                  | 1
agentless_managed_endpoint | 0

I tried

select * 
from tips_endpoints 
where description = 'Manual add from SCCM' 
AND attributes = '{"Our Device": "true"}';

but it fails.

I need to be able to find records where attributes has value = {"Our Device": "true"}

Upvotes: 1

Views: 134

Answers (2)

user330315
user330315

Reputation:

As the column is a jsonb column, a better approach would be to use one of the JSON functions

and attributes ->> 'Our Device' = 'true'

or

and attributes @> {"Our Device": "true"}

Both expressions can be indexed if necessary.

Upvotes: 1

Andrey Bessonov
Andrey Bessonov

Reputation: 348

Try this:

select * 
from tips_endpoints 
where description = 'Manual add from SCCM' 
  AND attributes Like '%{"Our Device": "true"}%';

UPD, for Postgres:

select * 
from tips_endpoints 
where description = 'Manual add from SCCM' 
  AND attributes::text Like '%{"Our Device": "true"}%';

Upvotes: 0

Related Questions