如何在PostgreSQL中创建和存储对象数组

Question

在PostgreSQL中，允许数组类型为整数和文本。但是我需要创建对象数组。我该怎么办？

myarray text[];   //for text ['a','b','c']
myarray integer[];  //for integer[1,2,3]

我需要创建如下的数组

[{'dsad':1},{'sdsad':34.6},{'sdsad':23}] 

我不想使用JSON类型。使用数组类型，我需要存储对象的数组。

- sachin

Answer 1

如果您运行的是Postgres 9.2+，您可以使用JSON类型。

例如，我们可以执行：

create table jsontest (id serial primary key, data json);
insert into jsontest (data) values ('[{"dsad":1},{"sdsad":34.6},{"sdsad":23}]');

使用 And 查询数据

select data->1 from jsontest;
{"sdsad":34.6}

- Daniel Lubarov

---

实际上，您也可以在Pg 9.1中获取JSON扩展。它只是没有默认安装。 - Chris Travers

Answer 2

您说：

我不想使用JSON类型

但是您不能使用普通数组，因为PostgreSQL数组必须是同种类型的。您不能拥有一个既包含文本又包含整数的二维数组。

如果您不想使用json，则可以创建一个组合类型：

CREATE TYPE my_pair AS (blah text, blah2 integer);

SELECT ARRAY[ ROW('dasd',2), ROW('sdsad', 34.6), ROW('sdsad', 23) ]::my_pair[]

这将会发出：

                 array                  
----------------------------------------
 {"(dasd,2)","(sdsad,35)","(sdsad,23)"}
(1 row)

如果您不想使用其他方法，那么json可能是最佳选择。或者hstore：

SELECT hstore(ARRAY['a','b','c'], ARRAY[1,2,3]::text[])

            hstore            
------------------------------
 "a"=>"1", "b"=>"2", "c"=>"3"
(1 row)

- Craig Ringer

Answer 3

JSON是您首选的答案，但需要更多信息以了解其原因。

您可以尝试以下操作：

SELECT array_agg(v) 
  FROM mytable v;

然而，您会得到类似于以下的东西：

{"(dsad,1)","(sdsad,34.6)","(""sdsad,var"",23)"}

然后，您需要知道如何解码这个东西（即列顺序）。虽然可以通过编程实现，但使用JSON会更容易。

- Chris Travers

Answer 4

这种方法可能有些取巧，但可以考虑为对象中的每个属性（及其相应的标量类型）使用一个数组。如果您在获取/读取数据时有一个数据模型层，您可以将这些数组“重新组合”成对象数组，并在保存方法中将对象分解为同步的数组。这可能会受到对象不具有相同属性的示例的复杂性的影响；除非您愿意使 null 在语义上相同，否则我不知道如何为属性存储未定义。

"原始答案"的翻译是"最初的回答"

- grantwparks

Answer 5

“不完全清楚您是否指的是JSON：”

# select '[{"dsad":1},{"sdsad":34.6},{"sdsad":23}]'::json;
                   json                   
------------------------------------------
 [{"dsad":1},{"sdsad":34.6},{"sdsad":23}]
(1 row)

或者是一个 JSON 数组：

# select array['{"dsad":1}', '{"sdsad":34.6}', '{"sdsad":23}']::json[];
                        array                         
------------------------------------------------------
 {"{\"dsad\":1}","{\"sdsad\":34.6}","{\"sdsad\":23}"}
(1 row)

或者使用hstore？如果使用后者，只能用于键值对，但您同样可以使用包含多个hstore值的数组。

- Denis de Bernardy

Answer 6

您可以这样做：

SELECT JSON_AGG(v) FROM mytable v;

但是您会得到类似于以下内容的结果：

["00000000-0000-0000-0000-000000000001","00000000-0000-0000-0000-000000000002", "00000000-0000-0000-0000-000000000003"]

例如：

SELECT title, (select JSON_AGG(v.video_id) FROM videos v WHERE v.channel_id = c.channel_id) AS videos FROM channel AS c

- Adrien Parrochia

Answer 7

使用text[] myarray代替myarray text[]。

- Dinesh ram