PostgreSQL verify an empty array on json

Question

I have the following row on select

jsonData
[]
[{"descricao":"falha na porta"}, {"descricao":"falha no ip"}]
[]

I have to Identify empty jsons, then manually add a value to it (eg row 1 and 3 ), I tried the following :

case when jsonData is null then cast('[{"descricao":"no error"}]' AS json) else jsonData   end as opts

But the "is null" verification fails for this type of data (array of json), how to identify '[]' values in this case?

Note: I only have select permission on this db

Laurenz Albe · Accepted Answer · 2022-01-27 21:29:26Z

5

Try this condition:

jsondata = JSON '[]'

edited Jan 27, 2022 at 21:29

answered Jan 27, 2022 at 14:59

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

Renan Fernandes Over a year ago

ERROR: operator does not exist: json = jsonb Hint: No operator matches the given name and argument types, also the same occurs when using these: jsondata = JSON '[]' and jsondata = '[]' :: json

Renan Fernandes Over a year ago

However worked using jsondata::text = '[]' , Your suggestion helped me reach this answer thanks.

user330315 Over a year ago

@RenanFernandes: the other option is to cast your json column to jsonb (which it should be anyways): jsondata::jsonb = jsonb '[]'

user330315 · Accepted Answer · 2022-01-27 20:47:13Z

4

You can use json_array_length()

when json_array_length(jsondata) = 0 then ...

answered Jan 27, 2022 at 20:47

user330315

Comments

Renan Fernandes · Accepted Answer · 2022-01-27 16:53:55Z

1

Casting the json to text before comparison worked for this case : " case when jsondata::text = '[]' "

answered Jan 27, 2022 at 16:53

Renan Fernandes

1971 gold badge3 silver badges14 bronze badges

Collectives™ on Stack Overflow

PostgreSQL verify an empty array on json

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related