使用node在postgresql中传递对象数组

6ojccjat  于 2023-03-01  发布在  PostgreSQL
关注(0)|答案(1)|浏览(94)
var query = `select * from map_balances where balance_userid = $1 AND balance_vendorid = $2 AND balance_amount >= $3`
                var values = [accesstoken,token,arr.map(e=>e.vid),arr.map(e=>e.amt)];
                client.query(query,{arr:values},async(err,projectdata)=>{
                    if (err) {
                        res.json({success:false,message:err.message,responsecode:-1});
                    } else {
                        res.json({success:true,message:projectdata.rowCount,responsecode:200});
                    }
                });

这里,arr=[{"vid": 636,"amt": "21","add": "123123"},{"vid": 636,"amt": "21","add": "123123"},{"vid": 636,"amt": "21","add": "123123"}]在客户端将是这样
这里我尝试从数据库获取数据,arr是一个数组,其中包含每个对象中的对象数“amt”,它应该大于数据库中的余额。我使用postgres,并尝试了一些其他类型,以及仍然不工作

qltillow

qltillow1#

如果你把数组json本身传递给PostgreSQL,它看起来像:

var query = `select mb.*
from map_balances mb
    inner join jsonb_to_recordset( $1::jsonb) as x(vid int, amt int, add int)
    on mb.balance_vendorid = x.vid AND
       mb.balance_amount >= x.amt AND
       mb.balance_userid = x.add;`

相关问题