Dynamic where clause based on input parameters in postgresql

Question

I have a jsonb parameter to my plsql procedure with data {1=2,2=3}. My Table contains the following data,

att_name |att_key |att_value
abcd     |1       |2
bcde     |2       |3
efgh     |1       |3

I want to filter out the data where (att_key = 1 and att_value = 2) or (att_key = 2 and att_value = 3) which should be dynamically generated based on the jsonb input.The output will contain abcd and bcde only. Is there any way to create this query dynamically in postgresql.

Kaushik Nayak · Accepted Answer · 2018-10-29 07:49:55Z

1

{1=2,2=3} is not a valid JSON.

I think you mean {"1":"2","2":"3"}.

You may use json_each_text to convert to key value pair (text) and compare easily.

SELECT * 
  FROM   t 
WHERE  ( att_key, att_value ) IN (SELECT inp.key :: INT, inp.VALUE :: INT 
     FROM   json_each_text('{"1":"2","2":"3"}') AS inp );

Demo

answered Oct 29, 2018 at 7:49

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Dynamic where clause based on input parameters in postgresql

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