How to select floating point data from json array in postgreSQL

Question

I have postgreSQL table 'prices' that contains column 'json_column', this column contains json array as given:

[
    {
        "price": 10.10,
        "createdBy": "test_user"
    },
    {
        "price": 14.99,
        "createdBy": "test_user"
    },
    {
        "price": 20.10,
        "createdBy": "test_user"
    }
]

I want to select all prices from that array that is grater than 15.0.

I did have few attempts like so:

select json_column->'price' as price_values
from prices
where json_column->'price'::numeric < 20.0;

but nothing seems to work for me, can I get any help with this as might seem simple query?:)

[EDIT] I did try this valid query as was suggested, but the response is empty, even without the WHERE part:

select json_column->>'price' as price_values
from prices
where (json_column->>'price')::numeric < 20.0;

The result I expect is something like this:

| price_values |
|--------------|
| 10.10        |
| 14.99        |

Thanks in advance!

Answer 1

If your array is on one row like mine:

 id |           json_column
----+----------------------------------
  1 | [                               +
    |     {                           +
    |         "price": 10.10,         +
    |         "createdBy": "test_user"+
    |     },                          +
    |     {                           +
    |         "price": 14.99,         +
    |         "createdBy": "test_user"+
    |     },                          +
    |     {                           +
    |         "price": 20.10,         +
    |         "createdBy": "test_user"+
    |     }                           +
    | ]

That decision will be as follows: we make a subquery in which we use 'json_array_elements'. Select data from the subquery into the price_values column, where using WHERE we select the desired values.

SELECT price_values
FROM(SELECT (json_array_elements(json_column)->'price') AS price_values
FROM prices) AS foo
WHERE price_values::text::numeric < 20.0
; 

Output:

 price_values
--------------
 10.10
 14.99