0

Say I have a JSONB column, with a GIN index on it.

I can use jsonb_object_keys() to fetch the keys for each row. But is there an efficient way of getting the unique set of keys, across all rows?

Surely the GIN index must have this information already, right?

NB: I know the "classic" solution: split the single column into a many-to-many relationship.

3
  • Are you looking for only top-level keys in the JSONB, or all keys?
    – jjanes
    Feb 22, 2019 at 18:52
  • @jjanes I only need the top-level keys.
    – scribu
    Feb 22, 2019 at 19:16
  • how many rows do you have, how often are they changing (new top level keys added, or old removed) Feb 25, 2019 at 3:14

1 Answer 1

2

This can't be done. Of the two built-in GIN operators for indexing JSONB, one of them only stores hashed values, so you wouldn't be able to reverse them, and other one stores flattened keys, irrespective of what level of the JSONB they were at. That second one also hashes the values if they exceed a certain length.

Both of the methods can lead to false positives, so both rely on "rechecking" the full JSONB in the tabe to make sure they meet the criteria.

For example, with the "jsonb_ops" operator, the value {"ABCD": {"EFGH": "IJKL"}} will initially match the query @> '{"EFGH":{}}', because the value does have "EFGH" as a key. It would then fail the recheck, because that key occurs at the wrong depth.

1
  • Thanks! Helpful to know not to bark at this tree anymore. :)
    – scribu
    Feb 24, 2019 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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