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

Question

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?

Laurenz Albe · Accepted Answer · 2022-04-19 08:55:41Z

2

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

answered Apr 19, 2022 at 8:55

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D-Shih · Accepted Answer · 2022-04-19 08:55:36Z

0

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

answered Apr 19, 2022 at 8:55

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related