PostgreSQL filter by column with json

Question

There is a json_agg resulting in [10,20] or [null]. I want to filter all rows with [null] in the column. How is it possible to achieve this?

Laurenz Albe · Accepted Answer · 2020-03-04 14:13:25Z

1

Sounds like a simple WHERE condition:

WHERE jsoncol::text = '[null]'

edited Mar 4, 2020 at 14:13

answered Mar 4, 2020 at 13:37

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

3 Comments

Dalitos Over a year ago

Then i get this error: ERROR: operator does not exist: json = json LINE 78: ) x WHERE x.geometry = '[null]'::json ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. SQL state: 42883 Character: 3910

user330315 Over a year ago

@Dalitos: you will need to use jsonb for that (on both sides)

Laurenz Albe Over a year ago

Sorry, I have fixed the answer.

Dalitos · Accepted Answer · 2020-03-04 13:46:57Z

0

i found this solution: jsoncol ->>0 IS Null

answered Mar 4, 2020 at 13:46

Dalitos

1091 silver badge9 bronze badges

3 Comments

Laurenz Albe Over a year ago

That would also be true for a JSON like [null,42,"x"].

Dalitos Over a year ago

hm, do you have another solution?

Laurenz Albe Over a year ago

Yes, see my answer.

Collectives™ on Stack Overflow

PostgreSQL filter by column with json

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related