PostgreSQL cannot call json_object_keys on a scalar

Question

I have a PostgreSQL table with a JSON column, and I'm trying to get a list of all the distinct keys in that column. I created a query to do this:

SELECT DISTINCT json_object_keys(j) FROM t;

Where t is the table and j is the JSON column. This worked on a small set of data correctly, it would list all the keys that exist in j, without repeating them. However, after adding a lot more data, it doesn't work anymore, giving the error:

ERROR:  cannot call json_object_keys on a scalar

I'm not sure exactly why this is happening. By just limiting the query to certain rows one at a time, I found one that causes the error. In that row, j is null. However, calling SELECT json_object_keys(null); does not cause this error, while calling SELECT json_object_keys(j) FROM t WHERE id=12; does, and j in this row is just null. I'm not really sure where to go from here.

So I guess my question is what could be causing this, and how can I either work around it or prevent it from happening?

Running PostgreSQL 9.3.9

Edit: Ok, I may have posted this a little preemptively. I figured out that j in the problematic row isn't null, it's 'null'::json, which wasn't clear from just selecting the row. This does cause the scalar error, so now I just have to figure out a way to select the rows where j isn't 'null'::json.

Answer 1

I tried this query, to filter out the 'null'::json values with this query:

SELECT DISTINCT json_object_keys(j) from t WHERE j <> 'null'::json;

However, apparently there is no json <> json operator, so I had to cast it to text and compare.

SELECT DISTINCT json_object_keys(j) from t WHERE j::TEXT <> 'null';

This works! I'm not a Postgres expert though, so this may not be the most effecient way of doing this check.