PostgreSQL query using json list of ids to second table-CodePudding

I have a table column that contains a list of user ids in json format (the column type is character varying at the moment):

mydb=> select value.id, value.text from value where value.id=385283;
   id   |   text    
-------- -----------
 385283 | [2,3,4]
(1 row)

I have figured out that I can expand it using the following query:

mydb=> select value.id, json_array_elements(cast(value.text as json)) as user_id from value where value.id=385283;
   id   | user_id 
-------- ---------
 385283 | 2
 385283 | 3
 385283 | 4
(3 rows)

Now I would like to retrieve the username from the user table, using the user ids returned by the previous query.

mydb=> select "user".username from "user" where "user".id in (2,3,4);
  username   
-------------
 Anonymous_2
 Anonymous_3
 Anonymous_4

I tried the query below (and several other ideas such as join, subquery) but I could not get it working:

mydb=> select "user".username from "user", value where value.id=385283 and "user".id in (json_array_elements(cast(value.text as json)));
ERROR:  set-returning functions are not allowed in WHERE
LINE 1: ...r", value where value.id=385283 and "user".id in (json_array...

Please advise how I could write a query for this seemingly trivial problem.

CodePudding user response：

Using set-returning functions in the SELECT list is allowed, but generally a bad idea. Put it in the FROM list.

select value.id, user_id, "user".username from 
   value CROSS JOIN  
   json_array_elements(cast(value.text as json)) f(user_id) JOIN
   "user" on "user".id=f.user_id
where value.id=385283;

Also, you should not use "user" or "value" as table names, or "text" as a column name. Not even in examples.