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

Question

I have column in postgreSQL 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 [].

Yes, I want to remove empty elements. I don't know why it is coming but when I want to query it, I don't want empty elements. — Kush Patel
– Kush Patel, Commented Jul 25, 2016 at 14:56
The real question is: why are you sometimes inserting an empty JSON object and sometimes and empty JSON array. — user330315
– user330315, Commented Jul 25, 2016 at 15:04

user1804599 · Accepted Answer · 2016-07-25 15:02:34Z

2

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

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

answered Jul 25, 2016 at 15:02

user1804599

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

Collectives™ on Stack Overflow

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

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