Using SELECT sub-query inside jsonb_array_elements_text function in Postgres

Question

I have the following query

SELECT DISTINCT ON (user_id) user_id, timestamp
FROM entries 
WHERE user_id in (1,2)
AND entry_type IN(
  SELECT jsonb_array_elements_text(
    SELECT entry_types 
    FROM users INNER JOIN orgs
    ON org_id = orgs.id 
    WHERE users.id = 1
  )
);

I'm getting a syntax error at or near select

syntax error at or near "select" LINE 1: ... entry_type in( select jsonb_array_elements_text(select ent.

The field entry_types is a JSONB field, so I am trying to convert it to text in order to use it in the WHERE IN clause.

PostgreSQL 13.0

This sub-query within jsonb_array_elements_text

SELECT entry_types 
    FROM users INNER JOIN orgs
    ON org_id = orgs.id 
    WHERE users.id = 1

Returns a single JSONB entry like this:

                entry_types                     
--------------------------------------------  
["type1", "type2", "type3"]

I'm simply trying to use the array of text values returned there as the criteria inside the WHERE IN clause.

Please provide sample data, desired results and an explanation of the purpose of the query. It is not obvious. — GMB

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-11-06T18:46:06

The syntax error seems to point somewhere else, so maybe I am wrong, but the problem I see is a missing pair of parentheses around the subquery:

jsonb_array_elements_text((SELECT ...))

Using SELECT sub-query inside jsonb_array_elements_text function in Postgres

1 Answers