SQL where clause for row_to_json output keys

I can select json key in step 2, but can't filter in step 3

How can I filter by key? Help me!

SQL is here:

-- 1) creatin开发者_运维技巧g view

drop if exists worker_responses_view
create or replace view worker_responses_view as
    select  
           row_to_json(hrm_orderresponse.*) as hrm_orderresponse_json,
           row_to_json(hrm_worker.*) as hrm_worker_json,
           row_to_json(hrm_orderperdayitem.*) as hrm_orderperdayitem_json,
           row_to_json(hrm_order.*) as hrm_order_json,
           row_to_json(hrm_branch.*) as hrm_branch_json
    from hrm_orderresponse, hrm_worker, hrm_orderperdayitem, hrm_order, hrm_company, hrm_branch
    where hrm_orderresponse.worker_id = hrm_worker.id
        and hrm_orderresponse.order_item_id = hrm_orderperdayitem.id
        and hrm_orderperdayitem.order_id = hrm_order.id
        and hrm_order.company_id = hrm_company.id
        and hrm_order.company_branch_id = hrm_branch.id;

this works

select hrm_orderresponse_json, hrm_orderresponse_json->>'worker_id' as worker_id
from worker_responses_view
limit 1;

but this

select hrm_orderresponse_json, hrm_orderresponse_json->>'worker_id' as worker_id
from worker_responses_view
where hrm_orderresponse_json->>'worker_id' = 1004;

results in:

HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

---

As documented in the manual the ->> operator returns a text value. But you are trying to compare that text value to an integer (comparing apples and oranges).

As the error message suggests, you need to change the type of one of them.

Either compare text to text:

hrm_orderresponse_json->>'worker_id' = '1004';

or cast the result:

(hrm_orderresponse_json->>'worker_id')::int = 1004;