Collect recursive JSON key paths In Postgres

Collect recursive JSON key paths In Postgres.sql

WITH RECURSIVE doc_key_and_value_recursive(key, value) AS (
  SELECT
    t.key,
    t.value
  FROM ideas, json_each(ideas.custom) AS t
  WHERE ideas.bucket_id = 889

  UNION ALL

  SELECT
    CONCAT(doc_key_and_value_recursive.key, '.', t.key),
    t.value
  FROM doc_key_and_value_recursive,
    json_each(CASE 
      WHEN json_typeof(doc_key_and_value_recursive.value) <> 'object' THEN '{}' :: JSON
      ELSE doc_key_and_value_recursive.value
    END) AS t
)
SELECT DISTINCT key
FROM doc_key_and_value_recursive
WHERE json_typeof(doc_key_and_value_recursive.value) NOT IN ('array', 'object')
ORDER BY key

Related SO question