Using JSONB_ARRAY_ELEMENTS with WHERE ... IN condition

Online poker players can optionally purchase access to playroom 1 or playroom 2.

And they can be temporarily banned for cheating.

CREATE TABLE users ( uid SERIAL PRIMARY KEY, paid1_until timestamptz NULL, -- may play in room 1 paid2_until timestamptz NULL, -- may play in room 2 banned_until timestamptz NULL, -- punished for cheating etc. banned_reason varchar(255) NULL ); 

Here the above table is filled with 4 test records:

INSERT INTO users (paid1_until, paid2_until, banned_until, banned_reason) VALUES (NULL, NULL, NULL, NULL), (current_timestamp + interval '1 month', NULL, NULL, NULL), (current_timestamp + interval '2 month', current_timestamp + interval '4 month', NULL, NULL), (NULL, current_timestamp + interval '8 month', NULL, NULL); 

All 4 records belong to the same person - who has authenticated herself via different social networks (for example through Facebook, Twitter, Apple Game Center, etc.)

I am trying to create a stored function, which would take a list of numeric user ids (as a JSON array) and merge records belonging to same person into a single record - without losing her payments or punishments:

CREATE OR REPLACE FUNCTION merge_users( IN in_users jsonb, OUT out_uid integer) RETURNS integer AS $func$ DECLARE new_paid1 timestamptz; new_paid2 timestamptz; new_banned timestamptz; new_reason varchar(255); BEGIN SELECT min(uid), current_timestamp + sum(paid1_until - current_timestamp), current_timestamp + sum(paid2_until - current_timestamp), max(banned_until) INTO out_uid, new_paid1, new_paid2, new_banned FROM users WHERE uid IN (SELECT JSONB_ARRAY_ELEMENTS(in_users)); IF out_uid IS NOT NULL THEN SELECT banned_reason INTO new_reason FROM users WHERE new_banned IS NOT NULL AND banned_until = new_banned LIMIT 1; DELETE FROM users WHERE uid IN (SELECT JSONB_ARRAY_ELEMENTS(in_users)) AND uid <> out_uid; UPDATE users SET paid1_until = new_paid1, paid2_until = new_paid2, banned_until = new_banned, banned_reason = new_reason WHERE uid = out_uid; END IF; END $func$ LANGUAGE plpgsql; 

Unfortunately, its usage results in the following error:

# TABLE users; uid | paid1_until | paid2_until | banned_until | banned_reason -----+-------------------------------+-------------------------------+--------------+--------------- 1 | | | | 2 | 2016-03-27 19:47:55.876272+02 | | | 3 | 2016-04-27 19:47:55.876272+02 | 2016-06-27 19:47:55.876272+02 | | 4 | | 2016-10-27 19:47:55.876272+02 | | (4 rows) # select merge_users('[1,2,3,4]'::jsonb); ERROR: operator does not exist: integer = jsonb LINE 6: WHERE uid IN (SELECT JSONB_ARRAY_ELEMENTS(in_users)) ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT min(uid), current_timestamp + sum(paid1_until - current_timestamp), current_timestamp + sum(paid2_until - current_timestamp), max(banned_until) FROM users WHERE uid IN (SELECT JSONB_ARRAY_ELEMENTS(in_users)) CONTEXT: PL/pgSQL function merge_users(jsonb) line 8 at SQL statement 

Please help me to solve the problem.

Here is a gist with SQL code for your convenience.

1 Answer

Result of jsonb_array_elements() is a set of jsonb elements, therefore you need add explicit cast of uid to jsonb with to_jsonb() function, IN will be replaced with <@ operator:

WITH t(val) AS ( VALUES ('[1,2,3,4]'::JSONB) ) SELECT TRUE FROM t,jsonb_array_elements(t.val) element WHERE to_jsonb(1) <@ element; 

For your case, snippet should be adjusted to something like:

...SELECT ...,JSONB_ARRAY_ELEMENTS(in_users) user_ids
WHERE to_jsonb(uid) <@ user_ids...