Mysql – indexable JSON datatype in MySQL/MariaDB

If you have a one-node cluster, add those settings to my.cnf

[mysqld]
binlog_format=ROW
innodb_autoinc_lock_mode=2

Login in a run

mysql> reset master;
mysql> set global innodb_fast_shutdown = 0;

Shutdown mysql

service mysql stop

Go to the folder where the binary logs are located and manually delete the binary logs (should only be one at this point) and also the binary log index file.

Start up MySQL

service mysql start

That's all you need to do.

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.