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?

Simo Kivistö Simo Kivistö · Accepted Answer · 2015-04-11T21:05:10

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

Select from JSONB field with WHERE clause

1 Answers