如何在PostgreSQL中将json对象作为列？ [英] How to get a json object as column in postgresql?

问题描述

我在mu PostgreSQL 9.05上有这些表：

I have these table on mu PostgreSQL 9.05:

表：核心
字段：名称，描述，数据

data 字段是一个json字段，例如： { id： 100，税： 4,5}

data field is a json field, with (for example): {"id": "100", "tax": "4,5"}

每个数据始终为个 json

Always is one json per data.

我的问题是：我可以将所有JSON字段作为查询字段吗？像这样返回：名称，描述，ID，税...。

My question is: can I get all JSON fields as query fields? return like these: name, description, id, tax....

问题是：我的JSON确实

The problem is: my JSON does have various fields, can be Id, tax or other.

推荐答案

您不能动态地执行此操作。您需要指定想要的列：

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.

另一个选择是在Postgres中创建一个对象类型，该对象类型表示JSON中的属性，例如

Another option is to create an object type in Postgres that represents the attributes in your JSON, e.g.

create type core_type as (id integer, tax numeric, price numeric, code varchar);

然后可以将JSON转换为该类型，并且JSON中的相应属性将自动转换为列：

You can then cast the JSON to that type and the corresponding attributes from the JSON will automatically be converted to columns:

具有上述类型和以下JSON： { id： 100， tax： 4.5，价格： 10，代码： YXCV} 您可以：

With the above type and the following JSON: {"id": "100", "tax": "4.5", "price": "10", "code": "YXCV"} you can do:

select id, (json_populate_record(null::core_type, data)).*
from core;

，它将返回：

id | tax  | price | code
---+------+-------+-----
 1 | 4.50 |    10 | YXCV

但是您需要确保可以转换每个JSON值

But you need to make sure that every JSON value can be cast to the type of the corresponding object field.

如果更改了对象类型，则使用该对象类型的任何查询都会自动更新。因此，您可以通过集中定义来管理感兴趣的列。

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.

这篇关于如何在PostgreSQL中将json对象作为列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！