3

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?

  • I can't reproduce that "cannot extract element from a scalar" error outcome on 9.4 using the json type. Can you show a self-contained statement that produces it, with data? – Craig Ringer Sep 30 '15 at 14:55
  • Just tested on SQLFiddle, can't reproduce the error on 9.3 either. What exactly do you do to get that error? Something like select ('{"k":"v"}'::json)->'v'->>'k'; returns null. – Craig Ringer Sep 30 '15 at 15:05
2

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.

  • 2
    I was looking for a backport from 9.4 to 9.3 (like there is one from 9.3 to 9.2), in vain. Apparently, there is at least one user needing it. Do you know about such a thing? – dezso Sep 30 '15 at 14:54
  • None I know of. I know someone's backported some of the 9.5 json/jsonb improvements to 9.4 though. It'd make sense to backport just the json improvements to 9.3, but most of what people want is really jsonb functionality and that's not practical to backport (pg_upgrade would not be happy if it was turned into an extension) – Craig Ringer Sep 30 '15 at 14:56
  • 1
    @dezso Extracting things like json_typeof might not be too bad, but it depends on how much of the new 9.4 json functionality depends internally on code tightly coupled to jsonb. I suspect quite a bit but I'm not keen to delve deeply into the json code at the moment. – Craig Ringer Sep 30 '15 at 15:01
0

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.

  • 2
    Also, there are chances (at least in theory, your data may not like that) that the '{' character itself is a part of a scalar. – dezso Sep 30 '15 at 14:46
  • Yes, however the regex could be anchored to the start of the string. A JSON string has to start with (possibly some spaces and) a ". A number has to start with - or a digit, etc. – jwg Sep 30 '15 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.