您如何显示查询（JSON）的多个结果？

Question

以下是从SQLite获取数据并将其显示为JSON的代码。

Service.js

const getSchedule = async ( operatorId ) => {
    const text = `
        SELECT "a.businessName"
            , "b.opTitle"
            , b.pay
            , "b.startTime"
            , "b.endTime"
            , "a.addressLine1"
            , "a.addressLine2"
            , "a.city"
            , "a.state"
            , "a.zip"
        from businesses a join ops b
        on a.id=b.businessId where b.operatorId= $1;
    `;
    const [ schedule ] = await query( text, [ operatorId ] );
    return schedule;
    
};

上述查询在SQLite的DB浏览器上正确工作。以下是我使用它作为结果发送数据的方式。

router.js：

.get( 
        '/:operatorId/schedules'
        , async ( req, res ) => {
            const operatorId = req.params.operatorId;
            const schedule = await getSchedule( operatorId );
            console.log(schedule)
            return res
                .status( 200 )
                .json( schedule )
        }
    )

SQL查询返回两个记录，我如何将其显示为JSON数据，因为当我尝试上述代码时，我会得到以下输出：

{
  '"a.businessName"': 'a.businessName',
  '"b.opTitle"': 'b.opTitle',
  pay: 200,
  '"b.startTime"': 'b.startTime',
  '"b.endTime"': 'b.endTime',
  '"a.addressLine1"': 'a.addressLine1',
  '"a.addressLine2"': 'a.addressLine2',
  '"a.city"': 'a.city',
  '"a.state"': 'a.state',
  '"a.zip"': 'a.zip'
}

我知道这不是正确的方法，而另一种方法是获得数据正常并在数据功能或路由器中进行操作，但是有一种方法可以这样做并获得预期的结果，这是：

[
    {
        "businessName": "Tesla"
        , "opTitle": "Replacing Tires"
        , "pay": 200.00
        , "startTime": "2021-10-21 10:00:00"
        , "endTime": "2021-10-21 15:55:00"
        , "addressLine1": "Something"
        , "addressLine2": "Line 2"
        , "city": "Dallas"
        , "state": "TX"
        , "zip": "7000"
    }
    , {
        "businessName": "Goodwill"
        , "opTitle": "Moving boxes of clothes"
        , "pay": 105.50
        , "startTime": "2021-10-22 10:00:00"
        , "endTime": "2021-10-22 15:55:00"
        , "addressLine1": "Something"
        , "addressLine2": "Line 2"
        , "city": "Irving"
        , "state": "TX"
        , "zip": "7000"
    }
]

谢谢

Answer 1

使用 sqlite的JSON函数 contracte to contregate：

SELECT JSON_GROUP_ARRAY( 
         JSON_OBJECT(
           'businessName', b.businessName,
           'opTitle', o.opTitle,
           'pay', o.pay,
           'startTime', o.startTime,
           'endTime', o.endTime,
           'addressLine1', b.addressLine1,
           'addressLine2', b.addressLine2,
           'city', b.city,
           'state', b.state,
           'zip', b.zip
         )
       ) AS result
FROM businesses b JOIN ops o
ON b.id = o.businessId 
WHERE o.operatorId= $1;

请参阅 demo 。