SQL where clause for row_to_json output keys

Question

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) creating 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.

user330315 · Accepted Answer · 2022-12-07 07:03:24Z

1

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;

answered Dec 7, 2022 at 7:03

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL where clause for row_to_json output keys

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related