PostgreSQL verify an empty array on json-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You can use json_array_length()

when json_array_length(jsondata) = 0 then ...

CodePudding user response：

Try this condition:

jsondata = JSON '[]'