0

I have a table with data like below,

keyId att_id att_value
1     1      4
1     2      5
2     1      4

I have my input to a procedure as a json {"1":"4","2":"5"}.

My procedure should return me the keyId which has both (att_id = 1 and att_value = 4) and (att_id=2 and att_value=5). So the output of this json input should be only 1 and not 2 as the second key value pair in json is not satisfied. Is there any way to dynamically append the and conditions inside a stored procedure in postgresql.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Sathish Kumar
  • 189
  • 1
  • 2
  • 14
  • And by "procedure" you mean a plpgsql function? Or just an SQL `SELECT` query? Please start by declaring your Postgres version. The input is exactly *two* key/value pairs or a variable number? If so, variable how exactly? – Erwin Brandstetter Oct 30 '18 at 01:07
  • After extracting the input values from your JSON format, it burns down to a case of relational division. See: https://stackoverflow.com/a/7828605/939860 – Erwin Brandstetter Oct 30 '18 at 01:34

1 Answers1

0

Assuming that att_id,att_value combination per KeyId has no duplicates, you may use GROUP BY and HAVING

SELECT  KeyID 
 FROM   t 
WHERE  ( att_id, att_value ) IN (SELECT inp.key :: INT, 
                                         inp.VALUE :: INT 
                                  FROM   json_each_text('{"1":"4","2":"5"}') AS 
                                         inp )
 GROUP BY  keyId HAVING COUNT(*) = 2;

Demo

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • No distinct would not work.. This will give me both 1 and 2 in the result set.. In clause is checking for OR clause internally and I need to check whether both the key value pairs are satisfied by a particular key id. – Sathish Kumar Oct 29 '18 at 09:32
  • @SathishKumar : Please check now. – Kaushik Nayak Oct 29 '18 at 09:55