Kppatel Patel Kppatel Patel - 4 months ago 8
JSON Question

How to remove {} and [] from json column postgresSQL

I have column in postgresSQL with json data type. Until today there were not row which contained {} or [].

However, I start to see {} and [] due to new implementation. I want to remove it.

Example: Following is my table looks like. json is json data type

id | json
----+------------------
a | {"st":[{"State": "TX", "Value":"0.02"}, {"State": "CA", "Value":"0.2" ...
----+------------------
b | {"st":[{"State": "TX", "Value":"0.32"}, {"State": "CA", "Value":"0.47" ...
----+------------------
d | {}
----+------------------
e | []


Where I want as following:

id | json
----+------------------
a | {"st":[{"State": "TX", "Value":"0.02"}, {"State": "CA", "Value":"0.2" ...
----+------------------
b | {"st":[{"State": "TX", "Value":"0.32"}, {"State": "CA", "Value":"0.47" ...


How I should able to do it ?

I have writen following query:

SELECT *
FROM tableA
WHERE json::text <> '[]'::text


Where I am able to filter empty elements which starts with {}. but still seeing [].

Answer

Very easy, just select all rows that don't contain those values:

SELECT *
FROM tableA
WHERE json :: text NOT IN ('{}', '[]')