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;
精彩评论