在Postgres中收集递归JSON键

Question

我在JSON数据类型（Postgres 9.3）下以Postgres存储了JSON文档，我需要递归地收集树下的键名。

例如，给定此JSON树

{
 "files": {
  "folder": {
   "file1": {
    "property": "blah"
   },
   "file2": {
    "property": "blah"
   },
   "file3": {
    "property": "blah"
   },
   "file4": {
    "property": "blah"
   }
 }
},
"software": {
  "apt": {
    "package1": {
        "version": 1.2
    },
    "package2": {
        "version": 1.2
    },
    "package3": {
        "version": 1.2
    },
    "package4": {
        "version": 1.2
    }
  }
 }
}

我想提取类似[file1，file2，file3，file3，package1，package2，package3，package4]的内容

基本上只列出了可用于文本搜索索引的键。

我知道我可以使用以下方法在最外面的对象上列出键

SELECT DISTINCT(json_object_keys(data))

而且我知道可以使用类似的方法递归爬过树

WITH RECURSIVE data()

但是我很难将两者放在一起。

有人可以帮忙吗？

Answer 1

诀窍是json_typeof在正确的位置添加一些最终条件测试。

jsonb如果您不关心对象键顺序，也应该使用。

这是我的工作环境：

CREATE TABLE test (
  id  SERIAL PRIMARY KEY,
  doc JSON
);

INSERT INTO test (doc) VALUES ('{
 "files": {
  "folder": {
   "file1": {
    "property": "blah"
   },
   "file2": {
    "property": "blah"
   },
   "file3": {
    "property": "blah"
   },
   "file4": {
    "property": "blah",
    "prop" : {
      "clap": "clap"
    }
   }
 }
},
"software": {
  "apt": {
    "package1": {
        "version": 1.2
    },
    "package2": {
        "version": 1.2
    },
    "package3": {
        "version": 1.2
    },
    "package4": {
        "version": 1.2
    }
  }
 }
}');

当第二个查询不返回任何行时，递归将停止。这是通过将空对象传递到来完成的json_each。

 WITH RECURSIVE doc_key_and_value_recursive(key, value) AS (
  SELECT
    t.key,
    t.value
  FROM test, json_each(test.doc) AS t

  UNION ALL

  SELECT
    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 *
FROM doc_key_and_value_recursive
WHERE json_typeof(doc_key_and_value_recursive.value) <> 'object';