postgresql 如何在postgresql中将json对象作为列获取？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39945308/
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 to get a json object as column in postgresql?
提问by fh_bash
I have these table on mu PostgreSQL 9.05:
我在 mu PostgreSQL 9.05 上有这些表：
Table: coreFields: name, description, data
表：core字段：name, description,data
datafield is a json field, with (for example): {"id": "100", "tax": "4,5"}
datafield 是一个 json 字段，具有（例如）： {"id": "100", "tax": "4,5"}
Always is onejson per data.
one每个数据始终是json。
My question is: can I get all JSON fields as query fields? return like these: name, description, id, tax....
我的问题是：我可以将所有 JSON 字段都作为查询字段吗？像这样返回：name, description, id, tax....
The problem is: my JSON does have various fields, can be Id, tax or other.
问题是：我的 JSON 确实有各种字段，可以是 Id、tax 或其他。
回答by a_horse_with_no_name
You can't do that "dynamically". You need to specify the columns you want to have:
你不能“动态地”做到这一点。您需要指定要拥有的列：
select name, description, id,
data ->> 'tax' as tax,
data ->> 'other_attribute' as other_attribute
from core;
If you do that a lot, you might want to put that into a view.
如果你经常这样做，你可能想把它放到一个视图中。
Another option is to create an object type in Postgres that represents the attributes in your JSON, e.g.
另一种选择是在 Postgres 中创建一个对象类型来表示你的 JSON 中的属性，例如
create type core_type as (id integer, tax numeric, price numeric, code varchar);
You can then cast the JSON to that type and the corresponding attributes from the JSON will automatically be converted to columns:
然后，您可以将 JSON 转换为该类型，JSON 中的相应属性将自动转换为列：
With the above type and the following JSON: {"id": "100", "tax": "4.5", "price": "10", "code": "YXCV"}you can do:
使用上述类型和以下 JSON:{"id": "100", "tax": "4.5", "price": "10", "code": "YXCV"}您可以执行以下操作：
select id, (json_populate_record(null::core_type, data)).*
from core;
and it will return:
它会返回：
id | tax | price | code
---+------+-------+-----
1 | 4.50 | 10 | YXCV
But you need to make sure that every JSON value canbe cast to the type of the corresponding object field.
但是您需要确保每个 JSON 值都可以转换为相应对象字段的类型。
If you change the object type, any query using it will automatically be updated. So you can manage the columns you are interested in, through a central definition.
如果您更改对象类型，任何使用它的查询都会自动更新。因此，您可以通过中央定义管理您感兴趣的列。
回答by Tamlyn
As of PostgreSQL 9.4 you can also use json_to_record.
从 PostgreSQL 9.4 开始，您还可以使用json_to_record.
Builds an arbitrary record from a JSON object (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause.
从 JSON 对象构建任意记录（请参阅下面的注释）。与所有返回记录的函数一样，调用者必须使用 AS 子句显式定义记录的结构。
For example:
例如：
select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text)
Returns
退货
a | b | d
---+---------+---
1 | [1,2,3] |
