Postgres Select Where JSON Field Not Null

Question

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;

Cᴏʀʏ · Accepted Answer · 2014-03-10 14:40:36Z

2

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;

answered Mar 10, 2014 at 14:40

Cᴏʀʏ

108k20 gold badges169 silver badges201 bronze badges

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

Collectives™ on Stack Overflow

Postgres Select Where JSON Field Not Null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related