在Postgres中收集递归JSON键 [英] Collect Recursive JSON Keys In Postgres
问题描述
我在JSON数据类型(Postgres 9.3)下以JSON数据类型存储了JSON文档,我需要递归地收集树下的键名.
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))
我知道可以使用
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屋!