CODEWITHSUNDEEP
jsonpostgresqlselect
lufthansa747
lufthansa747

Reputation: 2061

Postgres Select Where JSON Field Not Null

I have a table which has a column called warnings. the column value for 2 rows is shown bellow

                             warnings
-------------------------------------------------------------------
 {"isNew":false,"fieldWarnings":[],"dupId":null,"conflictIds":[]}
 {"isNew":false,"fieldWarnings":[],"dupId":3763,"conflictId":null}

I want an sql statement that will select the top row but not the bottom row. i tried this sql query but it selects both rows

select warnings from table where cast(warnings->>'dubId' AS TEXT) is null;

Upvotes: 1

Views: 3306

Answers (1)

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107508

You have dubId in your query but the JSON property is dupId. I think you've just got a typo!

Try this instead:

select warnings from table where cast(warnings->>'dupId' AS TEXT) is null;

Also, the ->> operator returns the field value as text already so you shouldn't need the cast:

select warnings from table where warnings->>'dupId' is null;

Upvotes: 2

Related Questions