Question 114084 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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"}

Tags:

2

Answers


  1. 0

    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"}%';
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search