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?

  • 3
    FWIW distinct isn't a function. Write SELECT DISTINCT jsonb_object_keys(...) to be clearer. – Craig Ringer Nov 27 '15 at 2:58
  • 1
    Essential missing information: version of Postgres, table definition, actual workflow. "ETL" is just a buzz word that doesn't define the use case. – Erwin Brandstetter Jul 6 '16 at 0:51
  • voted to close no input no one knows what he wants. – Evan Carroll Jan 12 '17 at 17:23

You could add a trigger on insert and update of each row which walks the keys of the new value and makes sure each key is in a side-table which lists all keys in any row of the main table.

Making sure that a key gets atomically deleted from the side-table when its last entry in the main table goes away would be much harder to do efficiently.

The GIN index created with the jsonb_ops operator class does contain the data you want, but as far as I know it provides no way for you to extract that info. (It also has the same problem with atomic deletes).

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.