在 Postgres 中收集递归 JSON 密钥 [英] Collect Recursive JSON Keys In Postgres

问题描述

我将 JSON 文档以 JSON 数据类型 (Postgres 9.3) 存储在 Postgres 中，我需要递归地收集树中的键名.

I have JSON documents stored in Postgres under the JSON data type (Postgres 9.3) and I need to recursively collect the key names down the tree.

例如，给定这个 JSON 树

For example, given this JSON tree

{
 "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] 的内容

I would like to extract something like [file1,file2,file3,file3,package1,package2,package3,package4]

基本上只是我可以用于文本搜索索引的键列表.

Basically just a listing of keys that I can use for a text search index.

我知道我可以使用类似的东西获取最外层对象上的键列表

I know I can get a listing of keys on the outer most objects using something like

SELECT DISTINCT(json_object_keys(data))

而且我知道可以使用类似

And I know it's possible to to recursively climb through the tree using something like

WITH RECURSIVE data()

但是我无法将两者放在一起.

but i'm having trouble putting the two together.

有人可以帮忙吗?

推荐答案

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

The trick is to add some final condition testing using json_typeof at the right place.

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

You should also be using jsonb if you don't care about object key order.

这是我的工作环境:

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 来完成的.

The recursion is stopped when the second query does not return any rows. This is done by passing an empty object to 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';

这篇关于在 Postgres 中收集递归 JSON 密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！