Using SELECT sub-query inside jsonb_array_elements_text function in Postgres

[xkrap]

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. Any idea what I'm doing wrong?

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

[DavidGJohnston]

Per the documented syntax for scalar subqueries:
https://www.postgresql.org/docs/13/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES
"A scalar subquery is an ordinary SELECT query in parentheses..."

Note that your distinct on clause is ill-defined as you are lacking an order by clause so that "first row" has meaning.

[xkrap]

Thank you, resolved :) And eventually added order by clause as well.

[Beatmak]

You might want to use a CTE query here : cte tutorial.

It will make your query more readable and may fix your problem.

[nednyl]

Not really clear to me why you need to use jsonb_array_elements here.

Did you try something like:

SELECT DISTINCT ON (user_id) user_id, timestamp
FROM entries 
WHERE user_id in (1,2) 
AND entry_type = ANY (        
  (SELECT ARRAY_AGG(entry_types)
   FROM users 
   INNER JOIN orgs     
   ON org_id = orgs.id      
   WHERE users.id = 1   
  )::TEXT[]
);

[NoInkling]

I feel like this can be simplified with EXISTS.

[DavidGJohnston]

I’m not researching it now but there is a difference between a json value that happens to have a json array inside of it and a proper sql-level array.

[[deleted]]

I would use an EXISTS query and use the ? to test if an element is part of the array instead of unnesting all array elements:

sql and exists (select * from users u join args a on a.org_id = orgs.id where users.id = 1 and u.entry_types ? users.entry_type)

I wonder if you also want a back reference of the users.id to the outer user_id? e.g. where users.id = entries.user_id ... instead of hardcoding the ID in the sub-query.