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

user_4738391 0
Viewed 6495

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.

jsonb postgresql ruby-on-rails sql json
2 Answers

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';
user_8785140
Related