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

Hi,

I come from the StackOverflow post you commented with your solution!

Combined with array solution, this code becomes really powerful!

Thank you very much for sharing it,

Théophile.

BTW : as said in the post, change ligne 12 to the following code in order to avoid table issues.
-----------------
CASE
WHEN (json_typeof(t.value)='array')
THEN json_array_elements(t.value)
ELSE t.value
END

---

Related SO question