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
– GMB, Commented Nov 6, 2020 at 18:31

Laurenz Albe · Accepted Answer · 2020-11-06 18:46:06Z

0

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 ...))

answered Nov 6, 2020 at 18:46

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Using SELECT sub-query inside jsonb_array_elements_text function in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related