CODEWITHSUNDEEP
jsonpostgresql
Dev Chakraborty
Dev Chakraborty

Reputation: 302

Get data type of JSON field in Postgres

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?

Upvotes: 14

Views: 8191

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42783

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;

Upvotes: 13

Dev Chakraborty
Dev Chakraborty

Reputation: 302

I ended up getting access to PLv8 in my environment, which made this easy:

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

As mentioned in the comments, there will be a native function for this in 9.4.

Upvotes: 0

Related Questions