Postgres: Flatten JSON keys and keys paths

query.sql

WITH RECURSIVE doc_key_and_value_recursive (
  KEY,
  value
) AS (
  SELECT
    t.key,
    t.value
  FROM
    stories_collection AS item,
    json_each(item.data) AS t
  WHERE
    item.id = 46
  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