14

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?

Dev Chakraborty
  • 303
  • 2
  • 10
  • 1
    Good question. Please raise it on pgsql-general for discussion; the json/jsonb feature devs hang out there, not here. We could use a `json[b]_value_kind` function or something. – Craig Ringer Nov 03 '14 at 08:55
  • You can also try casting the values, like described here: http://stackoverflow.com/a/16206123/1216680 – Houari Nov 03 '14 at 09:27
  • Good point, Houari. In my context, the problem is that "5" could potentially mean something different from 5. But your suggestion is still valid for most people who might run into this sort of thing. – Dev Chakraborty Nov 03 '14 at 09:44
  • 1
    Actually, there will be a function for that in PostgreSQL 9.4+: `json[b]_typeof(json[b])` http://www.postgresql.org/docs/9.4/static/functions-json.html -- until then, you can check the first non-whitespace character of the JSON representation (no need for robust regular expressions) – pozs Nov 03 '14 at 11:49

2 Answers2

11

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;
Daniel Lavedonio de Lima
  • 682
  • 2
  • 10
  • 20
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
0

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.

Dev Chakraborty
  • 303
  • 2
  • 10