[Query To Get JSON Field Names and Field Types]Using PostgreSQL JSONB functions to get JSON Structure #json #psql

query-json-field-names-and-field-types.sql

-- Returns table with JSON field name, field type, min, and max occurances
-- Change "your_source_table" to the table with the JSON/JSONB column(s)
--
-- CTE to get "list" of JSON object entities to analyze (here: location or items)
-- Assumes that column raw_order is of type JSONB
-- Modify with complex JSON field name in the CTE to match the one you want to analyze
--
WITH tp AS (
   SELECT s.raw_order->'location' AS raw_order                       -- location is an object field
   -- SELECT jsonb_array_elements(s.raw_order->'items') AS raw_order -- use this if location field is a array instead of an object
   FROM your_source_table s                                          -- change to point to your source table
   WHERE s.raw_order->>'location' IS NOT NULL                        -- only applies to object fields
)
--
-- Fixed Query to return table with JSON field name, field type, min, and max occurances
--
SELECT a.name, array_agg(DISTINCT jsonb_typeof(jsonb_extract_path(tp.raw_order, a.name))) AS json_type, min(a.cnt) AS cnt_min, max(a.cnt) AS cnt_max
FROM tp
, (
   SELECT jsonb_object_keys(tp.raw_order) AS name, count(*) AS cnt
   FROM tp
   GROUP BY jsonb_object_keys(tp.raw_order) 
   ORDER BY jsonb_object_keys(tp.raw_order)
) a
-- WHERE jsonb_typeof(jsonb_extract_path(tp.raw_order, a.name)) NOT IN ('object', 'array')  -- use this to exclude complex JSON fields
GROUP BY a.name ORDER BY a.name;