I want to achieve such a subquery, how to do it

[edcjian]

http://111.229.235.215:3000/multi_question?questionId=in.(paper_manage?select=questionId&paperId=eq.1002&questionType=eq.1)

[laurenceisla]

Looks like embedding in PostgREST won't filter the left table, for more complicated joins it's recommended to create custom VIEWS according to the docs. After joining multi_question and paper_manage in the VIEW multi_question_view, for instance, your query would look like this.

/multi_question_view?paperId=eq.1002&questionType=eq.1

Edit:
Taking into consideration what Steve mentioned below, a VIEW may not be your best choice and using a function is a better alternative. Something like:

CREATE FUNCTION get_multi_question(p_id int, q_type text) RETURNS TABLE (...) AS $$
  SELECT ... FROM multi_question WHERE questionId in (
    SELECT questionId 
    FROM paper_manage 
    WHERE paperId = p_id AND questionType = q_type
  );
$$ LANGUAGE SQL STABLE;

And your query would look like this:

GET /rpc/get_multi_question?p_id=1002&q_type=1

[steve-chavez]

I think what @edcjian is trying to do is more akin to

SELECT * FROM tbl WHERE id IN (SELECT other_id FROM other_tbl WHERE id = x)

That looks more fit for an RPC than a view - since it needs to parametrize x.

[laurenceisla]

Right, an RPC should be better. Edited my answer.

[wolfgangwalther]

CREATE FUNCTION get_multi_question(p_id int, q_type text) RETURNS TABLE (...) AS $$
  SELECT ... FROM multi_question WHERE questionId in (
    SELECT questionId 
    FROM paper_manage 
    WHERE paperId = p_id AND questionType = q_type
  );
$$ LANGUAGE SQL IMMUTABLE;

Careful - this function should for sure not be IMMUTABLE, but STABLE.

[laurenceisla]

True that. I fixed the answer.