Select from JSONB field with WHERE clause

Question

Just installed 9.4 and trying to use JSONB field type.

I've made a table with jsonb field and able to select from it:

select statistics->'statistics'->'all_trades'->'all'->'all_trades_perc_profit' as profitable_perc FROM  trade_statistics

Works fine.

Now I want to filter results based on field value:

select statistics->'statistics'->'all_trades'->'all'->'all_trades_perc_profit' as profitable_perc FROM  trade_statistics WHERE profitable_perc > 1

//There is no "profitable_perc" column

Does not work.

If I try to convert result to double, does not work either.

select cast(statistics->'statistics'->'all_trades'->'all'->'all_trades_perc_profit' as double precision) as profitable_perc FROM  trade_statistics

//cant convert jsonb into double precision

How should I use select results in WHERE clause in case of jsonb?

Answer 1

Three corrections have to be made:

• Wrap the the query in a subquery - you cannot reference the SELECT list aliases in WHERE clause
• Use the ->> operator to get the value as text

• Cast the text value as integer so you can make the comparison

  SELECT *
    FROM (
      SELECT (statistics->'statistics'->'all_trades'->'all'->>'all_trades_perc_profit')::integer as profitable_perc
          FROM  trade_statistics
    ) sq1
    WHERE profitable_perc > 1