Get JSON field data type in Postgres

Question

I have a Postgres JSON column in which some columns have data such as:

{"value":90}
{"value":99.9}

... while other columns have data such as:

{"value":"A"}
{"value":"B"}

An operator → (i.e. fields → 'value') will pass the JSON value, while an operator →> (i.e. fields →> 'value') passes the value to the text, as reported pg_typeof. Is there a way to find the “actual” JSON field data type?

My current approach would be to use Regex to determine if the appearance of fields →> 'value' in :: text fields is surrounded by double quotes.

Is there a better way?

Answer 1

PLv8 , :

CREATE FUNCTION value_type(fields JSON) RETURNS TEXT AS $$
    return typeof fields.value;
$$ LANGUAGE plv8;

, .