我正在使用postgres 9.2,我正在尝试构建一个以特定格式生成json的查询。我已经非常接近一个简单的json_agg
表达式,但现在我被困在前进。
CREATE TABLE project (
id INTEGER PRIMARY KEY,
name varchar(128) NOT NULL,
UNIQUE (name)
);
CREATE TABLE test (
id INTEGER PRIMARY KEY,
name varchar(128) NOT NULL,
project_id integer,
FOREIGN KEY (project_id) REFERENCES project(id),
);
CREATE TABLE data (
id INTEGER PRIMARY KEY,
date_entered timestamp with time zone NOT NULL,
data json NOT NULL,
test_id integer,
FOREIGN KEY (test_id) REFERENCES test(id)
);
插入数据后:
INSERT INTO project (id, name) VALUES (0, 'my_project');
INSERT INTO test (id, name, project_id) VALUES (0, 'test0', 0);
INSERT INTO data (date_entered, data, test_id) VALUES (TIMESTAMP WITH TIME ZONE '2014-04-15T09:34:41.454999 z', '["some", "data"]', 0);
INSERT INTO test (id, name, project_id) VALUES (1, 'test1', 0);
INSERT INTO data (date_entered, data, test_id) VALUES (TIMESTAMP WITH TIME ZONE '2014-04-15T09:34:41.454999 z', '["some", "data"]', 1);
我想构造一个查询,返回:
{
"test0": {
"first_data": "2014-04-15 09:35:10.394+00",
"data_points": 1
},
"test1": {
"first_data": "2014-04-15 09:35:10.394+00",
"data_points": 1
}
}
我得到的最接近这个解决方案是用这个查询:
SELECT
json_agg(data) as data
FROM (
SELECT
test.name as test_name,
min(data.date_entered) as first_data,
count(data.id) as data_points
FROM test
INNER JOIN data on data.test_id = test.id
INNER JOIN project on test.project_id = project.id
WHERE project.name = 'my_project'
GROUP BY test.name
) as data;
返回:
[
{
"test_name":"test0",
"first_data":"2014-04-15 09:34:41.454999+00",
"data_points":1
},
{
"test_name":"test1",
"first_data":"2014-04-15 09:34:41.454999+00",
"data_points":1
}
]
我尝试了各种奇怪的row_to_json和array_to_json的使用,但我似乎无法将test_name值转换为外部字典中的键。
这可能吗?我滥用postgres的json生成函数吗?
使用应用程序语言jsonify 确实可以做得更好。这里有一个丑陋的字符串连接解决方案
SQL小提琴select
(format(
'{"%s": {"first_data": "%s", "data_points": %s}}',
test.name,
min(data.date_entered),
count(data.id)
))::json as data
from test
inner join data on data.test_id = test.id
inner join project on test.project_id = project.id
where project.name = 'my_project'
group by test.name
SELECT jsonb_object_agg(test.name, jsonb_build_object('first_data', min(data.date_entered), 'data_points', count(data.id)))
FROM test
INNER JOIN data on data.test_id = test.id
INNER JOIN project on test.project_id = project.id
WHERE project.name = 'my_project'
GROUP BY test.name