PostgreSQL extract keys from jsonb, exception "cannot call jsonb_object_keys on a scalar"

Question

I am trying to get my head around with jsonb in Postgres. There are quite a few issues here, What I wanted to do was something like:

SELECT table.column->>'key_1' as a FROM "table"

I tried with -> and also some combinations of brackets as well, but I was always getting nil in a.

So I tried to get all keys first to see if it is even recognizing jsonb or not.

SELECT jsonb_object_keys(table.column) as a FROM "table"

This threw an error:

cannot call jsonb_object_keys on a scalar

So, to check the column type(which I created, so I know it IS jsonb, but anyway)

SELECT  pg_typeof(column) as a FROM "table" ORDER BY "table"."id" ASC LIMIT 1

This correctly gave me "jsonb" in the result.

values in the column are similar to {"key_1":"New York","key_2":"Value of key","key_3":"United States"}

So, I am really confused on what actually is going on here and why is it calling my json data to be scalar? What does it actually means and how to solve this problem?

Any help in this regard will be greatly helpful.

PS: I am using rails, posted this as a general question for the problem. Any rails specific solution would also work.

Answer 1

So the issue turned out to be OTHER than only SQL.

As I mentioned I am using rails(5.1), I had used default value '{}' for the jsonb column. And I was using a two-way serializer for the column by defining it in my model for the table.

Removing this serializer and adjusting the default value to {} actually solved the problem.

I think my serializer was doing something to the values, but still, in the database, it had correct value like i mentioned in the question.

It is still not 100% clear to me what was the problem. But it is solved anyway. If anyone can shed some light on what exactly the problem was, that will be great.

Hope this might help someone.

Answer 2

In my case the ORM layer somehow managed to wrote a null string into the JSON column and Postgres was happy with it. Trying to execute json_object_keys on such value resulted in the OP error.

I have managed to track down the place that allow such null strings and after fixing the code, I have also fixed the data with the following query:

UPDATE tbl SET col = '{}'::jsonb WHERE jsonb_typeof(col) <> 'object';

If you intentionally mix the types stored in the column (e.g. sometimes it is an object, sometimes array etc), you might want to filter out all rows that don't contain objects with a simple WHERE:

SELECT jsonb_object_keys(tbl.col) as a FROM tbl WHERE jsonb_typeof(col) = 'object';