nodejs postgres JSON返回嵌套数组的对象?

Question

寻求帮助。

我有一个基本的postgres select语句，它只是返回一个JSONb列。

*******编辑添加SQL查询************

anpool.query(`SELECT data as test
FROM appanalytics
WHERE to_date(data->>'dateTime', 'MM/DD/YYYY') 
BETWEEN $3 
AND $4
AND cid=$1 AND aid=$2`,[req.session.cID,req.session.AccountID,endDate,startDate]).then(analytics =>{
//This returns an array of objects, each object in the array 
// is wrapped in the column name as shown above
console.log(analytics)
});

这个很好不过数组中的每个对象都被包装在表名

中

[
{
test: {
ip: '10.10.10.10',
atID: 0,
uuID: '084B98DE-6D89-490E-8238-E25DDC8E5C04',
itemID: '0',
timeEnd: '2021-07-28T13:40:16.788Z',
actionID: 'speakerMainDetail',
dateTime: '07/28/2021',
location: [Object],
timeStart: '2021-07-27T12:54:21.284Z',
deviceType: 'web'
}
}
]

在这个响应中，你可以看到，对象嵌套在test中我需要它是顶层，所以每个响应对象都没有嵌套在test中，有什么想法吗?使用nodeJS和pg npm.

感谢

Answer 1

您将返回一个行数组，每个行都是包含您所选择的列的对象。没有什么你可以或应该做的反对-注意你不能使用ip,atID等作为列名，因为(大概)这些是动态的。你可以做

anpool.query(
`SELECT data as test
FROM appanalytics
WHERE to_date(data->>'dateTime', 'MM/DD/YYYY') 
BETWEEN $3 
AND $4
AND cid=$1 AND aid=$2`,
[req.session.cID,req.session.AccountID,endDate,startDate]
).then(result => {
const analytics = result.rows.map(row => row.test);
console.log(analytics);
});

Answer 2

您可以像这样提取它:

const res = [
{
test: {
ip: '10.10.10.10',
atID: 0,
uuID: '084B98DE-6D89-490E-8238-E25DDC8E5C04',
itemID: '0',
timeEnd: '2021-07-28T13:40:16.788Z',
actionID: 'speakerMainDetail',
dateTime: '07/28/2021',
location: [Object],
timeStart: '2021-07-27T12:54:21.284Z',
deviceType: 'web'
}
}
];
const { test } = res[0];
console.log(test);