How should I use `json field` in where clause in postgresql?

I have a json field in postgresql table feeAmount:

            feeAmount              |  id
------------------------------------+---------------
                                    | 001
 {"value": null, "currency": "AUD"}  | 002
 {"value": "5", "currency": "AUD"}  | 003

I’d like to query rows whose feeAmount -> value is not null. Some rows may be null for the entire feeAmount field. some rows’ feeAmount has a json data whose value is null. They need to be excluded.

I have tried this query:

select "feeAmount" from "Transactions" where "feeAmount"->'value' is not null;
select "feeAmount" from "Transactions" where "feeAmount"->'value'::text is not null;

but both of the queries return the rows

{"value": null, "currency": "AUD"}  | 002

how can I exclude the value: null from the query?

Answer

because feeAmount->'value' returns string null of json type

you can try cast feeAmount->'value' as string type before comparing.

SELECT *
FROM Transactions
WHERE (
 feeAmount->'value' is not null
AND 
 (feeAmount->'value')::text <> 'null'
)

or simple way use coalesce compare which row didn’t equal null text.

SELECT *
FROM Transactions
WHERE coalesce(feeAmount->'value','null') <> 'null'

sqlfiddle