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?

CodePudding user response：

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

CodePudding user response：

That would work like this:

WHERE "feeAmount" -> 'value' <> JSONB 'null'

(Use JSON instead of JSONB is your data type is json.)

That will exclude those rows where value is set to null. If you walso want to exclude rows where value is not set at all, you'd be better of with

WHERE "feeAmount" ->> 'value' IS NOT NULL