function json_each does not exist

Question

I am getting error that json_each function does not exist. I am using postgresql 9.3. I dont know whats wrong. Please assist me here.

select *
from json_each((
    select ed.result
    from externaldata ed
    inner join 
    application a
    on a.id = ed.application_id

))
limit 1;

The inside looped query returns :

" { "RespuestaSVC89":{
    "Header":{
      "Transaccion":"EXPE",
      "Servicio":"92",
      "CodigoRetorno":"00",
      "NumeroOperacion":"201409147001616",
      "CodigoModelo":"13852901"
    },
    "meta":{
      "billa":"EXPE",
      "numo":"52",
      "Retorno":"01",
      "Operacion":"2014091470",
    }
   }
}"

so it should work but somehow does not work

Exact error message is :

ERROR:  function json_each(text) does not exist
LINE 2: from json_each((
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********

ERROR: function json_each(text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 15

Answer 1

the error message states that no json_each(text) function exists, however I know that a json_each(json) function exists. The key is casting ed.result to json data type like so:

select *
from json_each((
    select ed.result::json
    from externaldata ed
    inner join 
    application a
    on a.id = ed.application_id

))
limit 1;

You might consider making ed.result column be of type json (in the actual table) instead of type text if your data truly is all valid json. When 9.4 comes out, you'll almost certainly want to use the jsonb data type to take advantage of the performance and space benefits that come with that datatype.

Answer 2

It might be one more possible reason:

A type of the column which contains a json is not of json type, but jsonb.

In this case you should use not json_each function, but jsonb_each.

Example:

create table metric
(
    user_id    bigint                   not null,
    created_at timestamp with time zone not null,
    data       jsonb                    not null,
    constraint metric_pk
        primary key (user_id, created_at)
);

Query:

select metric.created_at, jsb.key, jsb.value
from metric,
     json_each(data) as jsb
where user_id = <user_id>;

results in:

[42883] ERROR: function json_each(jsonb) does not exist

No function matches the given name and argument types. You might need to add explicit type casts.

Query

select metric.created_at, jsb.key, jsb.value
from metric,
     jsonb_each(data) as jsb
where user_id = <user_id>;

leads to the correct result: