PostgreSQL query using json list of ids to second table

Question

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.

" (the column type is character varying at the moment)" Why are you putting off doing it right? What are you waiting for? — jjanes
– jjanes, Commented Sep 13, 2022 at 16:10
The program supported only strings as data types at first. I then introduced more complex data types and converted the data to and from json in the java code. Now that I have learned that PostgreSQL directly supports json I will rewrite all queries accordingly and get rid of the java conversion code. Will also fix the table and column names. — nn4l
– nn4l, Commented Sep 13, 2022 at 17:16

jjanes · Accepted Answer · 2022-09-13 16:14:53Z

2

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.

answered Sep 13, 2022 at 16:14

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

1 Comment

nn4l Over a year ago

Thanks, I got the code working with two minor changes:select value.id, user_id, "user".username from value CROSS JOIN json_array_elements_text(cast(value.text as json)) f(user_id) JOIN "user" on "user".id=f.user_id::int where value.id=385283;

Collectives™ on Stack Overflow

PostgreSQL query using json list of ids to second table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related