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

#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;

#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;

#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;

#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;