Postgres cannot cast type jsonb to integer

Question

Threre are two tables.

Table1

 id integer
 color_name character(64) 

Table2

 id integer
 jdata jsonb

Json data looks like:

{"price": 4500, "colorId": 5}

I need output colors and count of items grouped by colors, so i tried to use this query:

SELECT Table1.color_name, Table2.jdata ->> 'colorId', count(*)
FROM Table1
INNER JOIN Table2
ON Table1.id = Table2.jdata ->> 'colorId'
group by Table2.jdata ->> 'colorId';

I get an error:

error: operator does not exist: integer = jsonb

Also i tried exec this:

select Table1.color_name, count(*) 
from Table1
join Table2
on (Table2.jdata->>'colorId')::int = Table1.id
group by Table1.color_name

What i get:

error: cannot cast type jsonb to integer

Solution 1

I think you need this:

select Table1.color_name, count(*) 
from Table1
join Table2
on (Table2.jdata->>'colorId')::int = Table1.id
group by Table1.color_name

Solution 2

Simple way to resolve this problem: select cast(value #>> '{}' as integer)

'value' is variable jsonb type.

For instance:

select  cast(to_jsonb('3'::text) #>> '{}' as integer)