PostgreSQL JSONB query types of each keys

Question

I have certain 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 query types of each key in that table, so it would give an output 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 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

that would give something like:

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

The json[b]_typeof(json[b]) function(s) give exactly, what you need (requires PostreSQL 9.4+). But I'm not sure, how you want to aggregate your results, f.ex. how do you want to represent the d | boolean:1 string:1 row? — pozs
– pozs, Commented Jul 24, 2015 at 21:03
This is the solution. Once I found one typeof function, I didn't think to go looking for a second. — Travis
– Travis, Commented Jul 25, 2015 at 2:07

Travis · Accepted Answer · 2015-07-25 02:06:10Z

EDIT:

As pozs points out, there are two typeof functions: one for JSON and one for SQL. This query is the one you're 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: (Hey, it works...)

This query will return the type of the keys:

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

... unfortunately, you'll notice that Postgres doesn't differentiate between the different JSON types. it regards it all 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 aren't that many JSON primitive types, and the output seems to be unambiguous. So this query will do what you're wanting:

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)

Inder · Accepted Answer · 2018-09-03 20:40:28Z

1

You can improve your last query with jsonb_typeof

with jsontypes as (
    SELECT
        json_data.key AS key1,
        jsonb_typeof(json_data.value) as jsontype
    FROM x, jsonb_each(x.data) AS json_data
)
select *, count(*)
from jsontypes
group by 1, 2
order by 1, 2;

edited Sep 3, 2018 at 20:40

Inder

3,8369 gold badges30 silver badges43 bronze badges

answered Sep 3, 2018 at 16:48

sdelobel

111 bronze badge

Collectives™ on Stack Overflow

PostgreSQL JSONB query types of each keys

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related