postrgres where in json array-CodePudding

I am planning to do a function. But don't even get to a Select Result.

I have a table "t_table1"

 id |              json_data              
---- ---------------------------------
  1 | {"key": "key1", "value": "3222"}    
  2 | {"key": "key1", "value": "224"}     
  3 | {"key": "key1", "value": "1243"}

In the function I want to send a json via PostgREST Api und receive as result the lines of the table. In the Post the json would look like:

{"ids":{[1,3]}}

the result would be:

 id |              json_data              
---- ---------------------------------
  1 | {"key": "key1", "value": "3222"}     
  3 | {"key": "key1", "value": "1243"}

I want to do it first with a select:

This works (with hardcoded ids):

CREATE OR REPLACE FUNCTION  api.query1(incoming_ids jsonb)
 RETURNS table  (id int4,json_data jsonb)
 LANGUAGE sql
 IMMUTABLE
AS $function$
SELECT * from t_table where  vid =ANY(ARRAY[1, 3])  
$function$

Select:

select api.query1('{"incoming_ids":[1,3]}')

But now I want of course this line "SELECT * from t_table where vid =ANY(ARRAY[1, 3])" Uses the incoming ids.

I have tried so much different thinks that would be too much Best

CodePudding user response：

Use jsonb_array_elements to parse the incoming array ids and compare it to the column vid, e.g

CREATE OR REPLACE FUNCTION api.query1(incoming_ids jsonb)
RETURNS table (id int4, json_data jsonb) LANGUAGE sql IMMUTABLE
AS $$
 SELECT vid,json_data FROM t_table
 CROSS JOIN LATERAL jsonb_array_elements($1::jsonb->'ids') j(ids)
 WHERE vid = ids::int4
$$

Demo: db<>fiddle

CodePudding user response：

It doesn't need to use ANY(), does it?

CREATE OR REPLACE FUNCTION  api.query1(incoming_ids jsonb)
 RETURNS table  (id int4,json_data jsonb)
 LANGUAGE sql
 IMMUTABLE
AS $function$
select * from t_table where id in (
SELECT x::int from jsonb_array_elements(incoming_ids)  x)
$function$

DBFiddle demo