如何在 postgresql 中创建和存储对象数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20095475/
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 create and store array of objects in postgresql
提问by sachin
In postgresql allowed array types or integer and text.But i need to create array of objects.how can i do that.
在 postgresql 中允许的数组类型或整数和文本。但我需要创建对象数组。我该怎么做。
myarray text[]; //for text ['a','b','c']
myarray integer[]; //for integer[1,2,3]
I need to create the array like below
我需要创建如下所示的数组
[{'dsad':1},{'sdsad':34.6},{'sdsad':23}]
I dont want to use JSON type.Using array type i need to store the array of objects.
我不想使用 JSON 类型。使用数组类型我需要存储对象数组。
回答by Daniel Lubarov
If you're running Postgres 9.2+, you can use the JSON type.
如果您运行的是 Postgres 9.2+，则可以使用 JSON 类型。
For example, we could do
例如，我们可以做
create table jsontest (id serial primary key, data json);
insert into jsontest (data) values ('[{"dsad":1},{"sdsad":34.6},{"sdsad":23}]');
And query the data with
并查询数据
select data->1 from jsontest;
{"sdsad":34.6}
回答by Craig Ringer
You say:
你说：
I dont want to use JSON type
我不想使用 JSON 类型
but you cannotuse an ordinary array, as PostgreSQL arrays must be of homogenous types. You can't have a 2-dimensional array of text and integer.
但是您不能使用普通数组，因为 PostgreSQL 数组必须是同构类型。您不能拥有文本和整数的二维数组。
What you could do if you don't want to use jsonis to create a composite type:
如果您不想使用，您可以做的json是创建一个复合类型：
CREATE TYPE my_pair AS (blah text, blah2 integer);
SELECT ARRAY[ ROW('dasd',2), ROW('sdsad', 34.6), ROW('sdsad', 23) ]::my_pair[]
which will emit:
这将发出：
array
----------------------------------------
{"(dasd,2)","(sdsad,35)","(sdsad,23)"}
(1 row)
If you don't want that, then jsonis probably your best bet. Or hstore:
如果您不想要那样，那么json可能是您最好的选择。或hstore：
SELECT hstore(ARRAY['a','b','c'], ARRAY[1,2,3]::text[])
hstore
------------------------------
"a"=>"1", "b"=>"2", "c"=>"3"
(1 row)
回答by grantwparks
It's hacky, but what about using an array for each property in the object (and its corresponding scalar type). If you have a data model layer in your get/read you could put the arrays "back together" into an array of objects and in your save method you would break you objects apart into synchronized arrays. This might be complicated by your example of each object not having the same properties; IDK how you'd store undefined for a property unless you're willing for null to be the same semantically.
这很hacky，但是如何为对象中的每个属性（及其相应的标量类型）使用一个数组呢？如果您的 get/read 中有一个数据模型层，您可以将数组“重新组合”到一个对象数组中，并且在您的 save 方法中，您可以将对象分解为同步数组。这可能会因您的每个对象不具有相同属性的示例而变得复杂；IDK 如何为属性存储 undefined ，除非您希望 null 在语义上是相同的。
回答by Denis de Bernardy
It's not entirely clear if you mean json:
如果你的意思是 json 并不完全清楚：
# select '[{"dsad":1},{"sdsad":34.6},{"sdsad":23}]'::json;
json
------------------------------------------
[{"dsad":1},{"sdsad":34.6},{"sdsad":23}]
(1 row)
Or an array of json:
或者一组json：
# select array['{"dsad":1}', '{"sdsad":34.6}', '{"sdsad":23}']::json[];
array
------------------------------------------------------
{"{\"dsad\":1}","{\"sdsad\":34.6}","{\"sdsad\":23}"}
(1 row)
Or perhaps hstore? If the latter, it's only for key-values pairs, but you can likewise use an array of hstore values.
或者也许是hstore？如果是后者，则它仅适用于键值对，但您同样可以使用 hstore 值数组。
回答by Chris Travers
JSON is your preferred answer, but more info as to why.
JSON 是您的首选答案，但有关原因的更多信息。
You can do something like:
您可以执行以下操作：
SELECT array_agg(v)
FROM mytable v;
However you get something that looks like this:
但是，您会得到如下所示的内容：
{"(dsad,1)","(sdsad,34.6)","(""sdsad,var"",23)"}
It is then up to you to know how to decode this (i.e. column order). This is possible to do programmatically but is much easier with JSON.
然后由您决定如何对其进行解码（即列顺序）。这可以通过编程方式完成，但使用 JSON 更容易。
