How to find the most frequently used JSON object keys, values, and key-value pairs inside a PostgreSQL table

Bjorn Krols

Bjorn Krols

Published on
30 March 2022

Lab setup

-- Create a table with a json column named "data"
CREATE TABLE my_table(data json);
-- Insert some rows
INSERT INTO my_table(data) VALUES
   ('{"key_a": "value_x"}'),
   ('{"key_a": "value_x"}'),
   ('{"key_a": "value_y"}'),
   ('{"key_a": "value_z"}'),
   ('{"key_b": "value_x"}'),
   ('{"key_c": "value_y"}'),
   ('{"key_d": "value_y"}');

Extracting key-value pairs

json_each expands the outermost JSON object into a set of key/value pairs. The returned value will be of type text.

-- Base
SELECT entry.key, entry.value FROM my_table, json_each_text(my_table.data) AS entry;
keyvalue
key_avalue_x
key_avalue_x
key_avalue_y
key_avalue_z
key_bvalue_x
key_cvalue_y
key_dvalue_y

Object keys ordered by frequency

-- Object keys ordered by frequency
SELECT entry.key, COUNT(*) AS count FROM my_table, json_each_text(my_table.data) AS entry GROUP BY entry.key ORDER BY count DESC;
keycount
key_a4
key_b1
key_c1
key_d1

Object values ordered by frequency

-- Object values ordered by frequency
SELECT entry.value, COUNT(*) AS count FROM my_table, json_each_text(my_table.data) AS entry GROUP BY entry.value ORDER BY count DESC;
valuecount
value_y3
value_x3
value_z1

Object key-value pairs ordered by frequency

-- Object key-value pairs ordered by frequency
SELECT entry.key, entry.value, COUNT(*) AS count FROM my_table, json_each_text(my_table.data) AS entry GROUP BY entry.key, entry.value ORDER BY count DESC;
keyvaluecount
key_avalue_x2
key_dvalue_y1
key_avalue_y1
key_bvalue_x1
key_cvalue_y1
key_avalue_z1

Subscribe to our newsletter

The latest news, articles, and resources, sent to your inbox weekly.

More like this