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.

fishsticks · Accepted Answer · 2015-07-29 19:17:26Z

15

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.

answered Jul 29, 2015 at 19:17

fishsticks

7531 gold badge6 silver badges16 bronze badges

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

3 Comments

fishsticks Over a year ago

Also, for anyone using SQLAlchemy, setting none_as_null=True on JSON columns completely eliminates this problem...

Peter Krauss Over a year ago

Use json_typeof(j)='array'.

Rohanthewiz Over a year ago

Thanks Peter. json_typeof() is a game-changer for me! Once I know the type of the value, I can do the correct extraction and operation on it as needed. select json_typeof('{"a":[1,2,3], "c":"some text", "d":true, "e":1000, "f": {"q": 124}}'::json#>'{a}')

Collectives™ on Stack Overflow

PostgreSQL cannot call json_object_keys on a scalar

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related