Use values from Postgres subquery that returns JSON arrays

Question

I have a table that contains a column that is JSON. In that json there is a key that contains an array of Ids.

I need to use those IDs to be returned as a flat array so that I can use them in an "IN" clause.

Here is where I am at:

SELECT id FROM prompts WHERE (id IN (array(SELECT properties->>'prompt_ids' FROM "prompts" WHERE (type = 'Aggregate'))));

That produces the following error:

ERROR:  operator does not exist: integer = text[]

When I run that subquery by itself I get:

SELECT properties->>'prompts' FROM "prompts" WHERE (type = 'Aggregate');
?column?
-----------
[15,16]
[3,49,50]
(2 rows)

I think I might need to write a function to convert those values in the array to integers, but I'm still not sure.

I'm using PostgreSQL 9.3.

Update 1

After playing around with casting functions I came up with a working, albeit crude, solution.

id IN (SELECT cast(cast(json_array_elements(properties->'prompts') as text) as integer))

worked for me.

Answer 1

Cast(convert) the "properties->>'prompt_ids'" into integer

SELECT id FROM prompts WHERE (id IN (array(SELECT properties->>'prompt_ids'::integer FROM "prompts"
WHERE (type = 'Aggregate'))));