如何从 Postgres 的 JSON 列中获取所有键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36141388/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get all keys from a JSON column in Postgres?
提问by verygoodsoftwarenotvirus
If I have a table with a column named json_stuff, and I have two rows with
如果我有一个名为 的表json_stuff，并且我有两行
{ "things": "stuff" }and { "more_things": "more_stuff" }
{ "things": "stuff" }和 { "more_things": "more_stuff" }
in their json_stuffcolumn, what query can I make across the table to receive [ things, more_things ]as a result?
在他们的json_stuff列中，我可以在整个表中进行什么查询以接收[ things, more_things ]结果？
回答by Sevanteri
Use this:
用这个：
select jsonb_object_keys(json_stuff) from table;
(Or just json_object_keysif you're using just json.)
（或者，json_object_keys如果您只使用 json。）
The PostgreSQL json documentation is quite good. Take a look.
PostgreSQL json 文档非常好。看一看。
And as it is stated in the documentation, the function only gets the outer most keys. So if the data is a nested json structure, the function will not return any of the deeper keys.
正如文档中所述，该函数仅获取最外层的键。因此，如果数据是嵌套的 json 结构，则该函数将不会返回任何更深的键。
回答by Dmitry Savinkov
WITH t(json_stuff) AS ( VALUES
('{"things": "stuff"}'::JSON),
('{"more_things": "more_stuff"}'::JSON)
)
SELECT array_agg(stuff.key) result
FROM t, json_each(t.json_stuff) stuff;
回答by natsuapo
Here is the example if you want to get the key list of each object:
如果您想获取每个对象的键列表，则示例如下：
select array_agg(json_keys),id from (
select json_object_keys(json_stuff) as json_keys,id from table) a group by a.id
Here idis the identifier or unique value of each row. If the row cannot be distinguished by identifier, maybe it's better to try PL/pgSQL.
这id是每行的标识符或唯一值。如果无法通过标识符区分行，也许最好尝试PL/pgSQL.
