Get data type of JSON field in Postgres

Question

I have a Postgres JSON column where some columns have data like:

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

...whereas other columns have data like:

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

The -> operator (i.e. fields->'value') would cast the value to JSON, whereas the ->> operator (i.e. fields->>'value') casts the value to text, as reported by pg_typeof. Is there a way to find the "actual" data type of a JSON field?

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

Is there a better way?

Answer 1

As @pozs mentioned in comment, from version 9.4 there are available json_typeof(json) and jsonb_typeof(jsonb) functions

Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.

https://www.postgresql.org/docs/current/functions-json.html

Applying to your case, an example of how this could be used for this problem:

SELECT
    json_data.key,
    jsonb_typeof(json_data.value) AS json_data_type,
    COUNT(*) AS occurrences
FROM tablename, jsonb_each(tablename.columnname) AS json_data
GROUP BY 1, 2
ORDER BY 1, 2;