I'm looking for a way to index / optimize finding only the keys in a JSONB column. The use case is an ETL operation where I need to take data stored in the JSONB column and output it as delimited text with headers (the keys in this case). The tables have millions of rows, so caching or iterating over all data to discover the complete set of keys isn't practical. The library I'm using needs the headers up front.
I can do the following query which works but will be impractically slow at scale.
SELECT DISTINCT jsonb_object_keys(jsondata)
FROM foo
This query on a 200k row table takes about 3 minutes to run. I have tables an order of magnitude larger than that. An explain shows an obviously undesirable full table scan:
Unique (cost=42283418.97..42372645.97 rows=198454 width=1282)
-> Sort (cost=42283418.97..42328032.47 rows=19845400 width=1282)"
Sort Key: (jsonb_object_keys(jsondata))"
-> Seq Scan on "foo" (cost=0.00..121283.40 rows=19845400 width=1282)
Is there a way to index only the keys in a JSON/JSONB field?
distinct
isn't a function. WriteSELECT DISTINCT jsonb_object_keys(...)
to be clearer.