Can I find all JSON scalars in PostgreSQL 9.3?

Question

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?

Answer 1

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

---

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column? 
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.

Answer 2

One approach is to check the string form of the JSON column. An object should start with '{', or maybe by some spaces followed by that character. So

SELECT *
FROM data_write
WHERE comment::text NOT SIMILAR TO ' *{%'

should catch most cases. However, unlike the JSONB column, JSON columns store JSON in its original form, not in a canonical form. So this method doesn't exclude the possibility that there will be some weird JSON, for example starting with another space character, which won't be matched.