PostgreSQL JSONB query types for each key

Question

I have a specific table:

CREATE TABLE x( id BIGSERIAL PRIMARY KEY, data JSONB ); INSERT INTO x(data) VALUES( '{"a":"test", "b":123, "c":null, "d":true}' ), ( '{"a":"test", "b":123, "c":null, "d":"yay", "e":"foo", "f":[1,2,3]}' ); 

How to request the types of each key in this table so that it produces a result something like this:

 a | string:2 b | number:2 c | null:2 d | boolean:1 string:1 e | string:1 f | jsonb:1 -- or anything 

I only know the way to get the keys and count, but I don’t know how to get the type of each key:

 SELECT jsonb_object_keys(data), COUNT(id) FROM x GROUP BY 1 ORDER BY 1 

which will give something like:

 a | 2 b | 2 c | 2 d | 2 e | 1 f | 1 

Answer 1

EDIT:

As pozs points out, there are two typeof functions: one for JSON and one for SQL. This request is the one you are looking for:

 SELECT json_data.key, jsonb_typeof(json_data.value), count(*) FROM x, jsonb_each(x.data) AS json_data group by key, jsonb_typeof order by key, jsonb_typeof; 

Old answer: (Hi, it works ...)

This query will return the key type:

 SELECT json_data.key, pg_typeof(json_data.value), json_data.value FROM x, jsonb_each(x.data) AS json_data; 

... Unfortunately, you will notice that Postgres does not distinguish between different types of JSON. it treats everything as jsonb , so the results are:

  key1 | value1 | value ------+--------+----------- a | jsonb | "test" b | jsonb | 123 c | jsonb | null d | jsonb | true a | jsonb | "test" b | jsonb | 123 c | jsonb | null d | jsonb | "yay" e | jsonb | "foo" f | jsonb | [1, 2, 3] (10 rows) 

However, there are not many primitive JSON types , and the result seems unambiguous. So this query will do what you want:

 with jsontypes as ( SELECT json_data.key AS key1, CASE WHEN left(json_data.value::text,1) = '"' THEN 'String' WHEN json_data.value::text ~ '^-?\d' THEN CASE WHEN json_data.value::text ~ '\.' THEN 'Number' ELSE 'Integer' END WHEN left(json_data.value::text,1) = '[' THEN 'Array' WHEN left(json_data.value::text,1) = '{' THEN 'Object' WHEN json_data.value::text in ('true', 'false') THEN 'Boolean' WHEN json_data.value::text = 'null' THEN 'Null' ELSE 'Beats Me' END as jsontype FROM x, jsonb_each(x.data) AS json_data -- Note that it won't work if we use jsonb_each_text here because the strings won't have quotes around them, etc. ) select *, count(*) from jsontypes group by key1, jsontype order by key1, jsontype; 

Output:

  key1 | jsontype | count ------+----------+------- a | String | 2 b | Integer | 2 c | Null | 2 d | Boolean | 1 d | String | 1 e | String | 1 f | Array | 1 (7 rows)