postrgres where in json array

Question

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  id =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

Jim Jones · Accepted Answer · 2022-07-19 14:15:33Z

2

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

answered Jul 19, 2022 at 14:15

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

2 Comments

Zio Over a year ago

Hi also for you: First: DBFiddle Cool Tool!! Thanks Now I see the problem. I am running on Postgres 10. Then it doesen't work. It works as of Version PG11. We will do a upgrade. But I think in autumm. Do you may have a solution for the PG10?

Jim Jones Over a year ago

@Zio perhaps this will help you: dbfiddle.uk/…

Cetin Basoz · Accepted Answer · 2022-07-20 10:30:23Z

0

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

EDIT: In postgres 10 there is a slight modified version. Instead of direct cast as x::int, you use a double cast like x::text::int.

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::text::int from jsonb_array_elements(incoming_ids)  x)
$function$

DbFiddle demo for postgres 10

edited Jul 20, 2022 at 10:30

answered Jul 19, 2022 at 14:39

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

2 Comments

Zio Over a year ago

Hi, First: DBFiddle Cool Tool!! Thanks Now I see the problem. I am running on Postgres 10. Then it doesen't work. It works as of Version PG11. We will do a upgrade. But I think in autumm. Do you may have a solution for the PG10?

Cetin Basoz Over a year ago

@Zio, in postgres 10 you need x::text::int instead, Edited and added dbfiddle.

Collectives™ on Stack Overflow

postrgres where in json array

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related