We are using Postgres 9.3 and making use of the JSON column which was new to that version. Version 9.4 added a LOT of utility functions for handling JSON, as well as the new JSONB column. You can see this by comparing http://www.postgresql.org/docs/9.4/static/functions-json.html with http://www.postgresql.org/docs/9.3/static/functions-json.html
9.4 has a function,
json_typeof which returns the type of a JSON object (as a string: 'string', 'number', 'array', 'object', etc.) I would like to know if there is any practical way in 9.3 of retrieving all rows which are scalars (or, equally useful for us, which are not objects).
We would like the
comment column of a given table to always be a JSON object. This means that we can do queries like:
SELECT comment->>'author' as author_name FROM our_table
If for some row the JSON in the comment column is an object, but which does not have any such key, you simply get back a NULL.
However, if there is even one scalar in the table, the query fails with the error
ERROR: cannot extract element from a scalar SQL state: 22023
This is a pain, as we have had strings written to this column some of the time, mainly due to errors. Is there any reasonable way of either filtering these out in a query, or identifying them all so that they can be removed in one go?